The count number of inner join

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

he he

Dear Folk,

The problem is when doing the count number like this SQL : example,
the field 'NUM' is the running num from 1 to 10, for example.
SELECT a.*, b.NUM
FROM CollectionFGREFNO AS a left outer JOIN COUNTABLE as b ON b.NUM = a.GNO;

or change to :

SELECT a.*, b.NUM
FROM CollectionFGREFNO AS a left outer JOIN COUNTABLE as b ON b.NUM <= a.GNO;

For the result is to merge both of the results, there should be no other out
flows of records while still results of the same orinal sources as the first
SQL.

Thanks
 
Hi,


You can't use * in a GROUP BY query, but


SELECT a.pk, LAST(a.whatever), COUNT(b.num)
FROM CollectionFGREFNO AS a left outer JOIN COUNTABLE as b
ON b.NUM <= a.GNO
GROUP BY a.pk



should count the number of records in b, for each records in a, for those
records in b that satisfy the comparison.




Hoping it may help,
Vanderghast, Access MVP
 
Thanks

Excuse me, I don't expect the 'pk' .

SELECT A.REFNO, Count(*) AS GNO, sum([value]) AS vaue
FROM sample AS A
GROUP BY A.REFNO;

This above SQL is named 'GREFNO'
Then on the 'CollectionFGREFNO' to produce the selection of both tables
with only the condition of where GREFNO.REFNO = sample.REFNO

to produce the 'CollectionFGREFNO'

SELECT GREFNO.REFNO,GREFNO.vaue, sample.VALUE
FROM GREFNO INNER JOIN sample ON GREFNO.REFNO = sample.REFNO
ORDER BY GREFNO.REFNO;

After that to continue to compare for the counting with the table
'COUNTABLE' with the field 'NUM' counting 1 to 10.

SELECT a.*, b.NUM
I wish to have them output as :

vaue value refno num
4 2 1 1
4 1 1 2
4 1 1 3
3 3 2 1
8 4 3 1
8 3 3 2
8 1 3 3

That's it.... and continued to the last...
Thanks,
 
Hi,


If I understand, you have two identical rows, (4, 1, 1) = (vaue value
refno ); you cannot get different result, num, for each row
vaue value refno num
Click to expand...
4 1 1 2
4 1 1 3
Click to expand...



as long as, indeed, the rows are absolutely identical. Is there a fourth
field, in addition to (vaue value refno ) that make the row unique? that
is why a primary key is often quite useful.



Hoping it may help,
Vanderghast, Access MVP



ooxx said:
Thanks

Excuse me, I don't expect the 'pk' .

SELECT A.REFNO, Count(*) AS GNO, sum([value]) AS vaue
FROM sample AS A
GROUP BY A.REFNO;

This above SQL is named 'GREFNO'
Then on the 'CollectionFGREFNO' to produce the selection of both tables
with only the condition of where GREFNO.REFNO = sample.REFNO

to produce the 'CollectionFGREFNO'

SELECT GREFNO.REFNO,GREFNO.vaue, sample.VALUE
FROM GREFNO INNER JOIN sample ON GREFNO.REFNO = sample.REFNO
ORDER BY GREFNO.REFNO;

After that to continue to compare for the counting with the table
'COUNTABLE' with the field 'NUM' counting 1 to 10.

SELECT a.*, b.NUM
I wish to have them output as :

vaue value refno num
4 2 1 1
4 1 1 2
4 1 1 3
3 3 2 1
8 4 3 1
8 3 3 2
8 1 3 3

That's it.... and continued to the last...
Thanks,



Michel Walsh said:
Hi,


You can't use * in a GROUP BY query, but


SELECT a.pk, LAST(a.whatever), COUNT(b.num)
FROM CollectionFGREFNO AS a left outer JOIN COUNTABLE as b
ON b.NUM <= a.GNO
GROUP BY a.pk



should count the number of records in b, for each records in a, for those
records in b that satisfy the comparison.




Hoping it may help,
Vanderghast, Access MVP
Click to expand...
Click to expand...
 
oh oh oh ..

Dear,

Yes, there is a field one named "id" as a primary key. And the original
Table fields :
id as primary vaue as sum of value value property as number and refno :

The sources are:
id vaue value refno
1 4 2 1
2 4 2 1
3 3 3 2
5 3 2 3
6 3 1 3

and to make it ranks by group:

num id vaue value refno
1 1 4 2 1
2 2 4 2 1
1 3 3 3 2
1 5 3 2 3
2 6 3 1 3

..... continued to the last...
Any idea
Thanks



Michel Walsh said:
Hi,


If I understand, you have two identical rows, (4, 1, 1) = (vaue value
refno ); you cannot get different result, num, for each row
vaue value refno num
Click to expand...
4 1 1 2
4 1 1 3
Click to expand...



as long as, indeed, the rows are absolutely identical. Is there a fourth
field, in addition to (vaue value refno ) that make the row unique? that
is why a primary key is often quite useful.



Hoping it may help,
Vanderghast, Access MVP



ooxx said:
Thanks

Excuse me, I don't expect the 'pk' .

SELECT A.REFNO, Count(*) AS GNO, sum([value]) AS vaue
FROM sample AS A
GROUP BY A.REFNO;

This above SQL is named 'GREFNO'
Then on the 'CollectionFGREFNO' to produce the selection of both tables
with only the condition of where GREFNO.REFNO = sample.REFNO

to produce the 'CollectionFGREFNO'

SELECT GREFNO.REFNO,GREFNO.vaue, sample.VALUE
FROM GREFNO INNER JOIN sample ON GREFNO.REFNO = sample.REFNO
ORDER BY GREFNO.REFNO;

After that to continue to compare for the counting with the table
'COUNTABLE' with the field 'NUM' counting 1 to 10.

SELECT a.*, b.NUM
FROM CollectionFGREFNO AS a left outer JOIN COUNTABLE as b ON b.NUM =
a.GNO;
Click to expand...

I wish to have them output as :

vaue value refno num
4 2 1 1
4 1 1 2
4 1 1 3
3 3 2 1
8 4 3 1
8 3 3 2
8 1 3 3

That's it.... and continued to the last...
Thanks,



Michel Walsh said:
Hi,


You can't use * in a GROUP BY query, but


SELECT a.pk, LAST(a.whatever), COUNT(b.num)
FROM CollectionFGREFNO AS a left outer JOIN COUNTABLE as b
ON b.NUM <= a.GNO
GROUP BY a.pk



should count the number of records in b, for each records in a, for those
records in b that satisfy the comparison.




Hoping it may help,
Vanderghast, Access MVP


he he

Dear Folk,

The problem is when doing the count number like this SQL : example,
the field 'NUM' is the running num from 1 to 10, for example.
SELECT a.*, b.NUM
FROM CollectionFGREFNO AS a left outer JOIN COUNTABLE as b ON b.NUM =
a.GNO;

or change to :

SELECT a.*, b.NUM
FROM CollectionFGREFNO AS a left outer JOIN COUNTABLE as b ON b.NUM <=
a.GNO;

For the result is to merge both of the results, there should be no
other
out
flows of records while still results of the same orinal sources as the
first
SQL.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Hi,


so, the ranking occurs among each value of vaue (from 1 to the number of
values in the group), with decreasing value of field value


SELECT a.id, LAST(a.vaue), LAST(a.value), LAST(a.refno), COUNT(*)

FROM myTable As a INNER JOIN myTable As b
ON a.vaue = b.vaue
AND (a.value < b.value OR
( a.value = b.value AND
(a.refno < b.refno OR
(a.refno = b.refno AND a.id<=b.id)
)))

GROUP BY a.id





The complex ON clause try to define the "ordering". A record, a, will occur
before another one, b, if
( its value < other record value OR
( their value is equal AND
( ...


The comparison (ordering) occurs only on the records having the same vaue
value.


Hoping it may help,
Vanderghast, Access MVP


ooxx said:
oh oh oh ..

Dear,

Yes, there is a field one named "id" as a primary key. And the original
Table fields :
id as primary vaue as sum of value value property as number and refno :

The sources are:
id vaue value refno
1 4 2 1
2 4 2 1
3 3 3 2
5 3 2 3
6 3 1 3

and to make it ranks by group:

num id vaue value refno
1 1 4 2 1
2 2 4 2 1
1 3 3 3 2
1 5 3 2 3
2 6 3 1 3

.... continued to the last...
Any idea
Thanks



Michel Walsh said:
Hi,


If I understand, you have two identical rows, (4, 1, 1) = (vaue value
refno ); you cannot get different result, num, for each row
vaue value refno num
Click to expand...
4 1 1 2
4 1 1 3
Click to expand...



as long as, indeed, the rows are absolutely identical. Is there a fourth
field, in addition to (vaue value refno ) that make the row unique?
that
is why a primary key is often quite useful.



Hoping it may help,
Vanderghast, Access MVP



ooxx said:
Thanks

Excuse me, I don't expect the 'pk' .

SELECT A.REFNO, Count(*) AS GNO, sum([value]) AS vaue
FROM sample AS A
GROUP BY A.REFNO;

This above SQL is named 'GREFNO'
Then on the 'CollectionFGREFNO' to produce the selection of both
tables
with only the condition of where GREFNO.REFNO = sample.REFNO

to produce the 'CollectionFGREFNO'

SELECT GREFNO.REFNO,GREFNO.vaue, sample.VALUE
FROM GREFNO INNER JOIN sample ON GREFNO.REFNO = sample.REFNO
ORDER BY GREFNO.REFNO;

After that to continue to compare for the counting with the table
'COUNTABLE' with the field 'NUM' counting 1 to 10.

SELECT a.*, b.NUM
FROM CollectionFGREFNO AS a left outer JOIN COUNTABLE as b ON b.NUM
=
a.GNO;

I wish to have them output as :

vaue value refno num
4 2 1 1
4 1 1 2
4 1 1 3
3 3 2 1
8 4 3 1
8 3 3 2
8 1 3 3

That's it.... and continued to the last...
Thanks,



:

Hi,


You can't use * in a GROUP BY query, but


SELECT a.pk, LAST(a.whatever), COUNT(b.num)
FROM CollectionFGREFNO AS a left outer JOIN COUNTABLE as b
ON b.NUM <= a.GNO
GROUP BY a.pk



should count the number of records in b, for each records in a, for
those
records in b that satisfy the comparison.




Hoping it may help,
Vanderghast, Access MVP


he he

Dear Folk,

The problem is when doing the count number like this SQL : example,
the field 'NUM' is the running num from 1 to 10, for example.
SELECT a.*, b.NUM
FROM CollectionFGREFNO AS a left outer JOIN COUNTABLE as b ON b.NUM
=
a.GNO;

or change to :

SELECT a.*, b.NUM
FROM CollectionFGREFNO AS a left outer JOIN COUNTABLE as b ON b.NUM
<=
a.GNO;

For the result is to merge both of the results, there should be no
other
out
flows of records while still results of the same orinal sources as
the
first
SQL.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
wow...

Thanks.. It's work like music .

Dear,
In the query, you have given, it works , but something in question of me
on how to make order of the ranks.

On your query, your order according to the field 'value' descending, on
my question is Can we order by field "id" ascending ...

Thanks.

Michel Walsh said:
Hi,


so, the ranking occurs among each value of vaue (from 1 to the number of
values in the group), with decreasing value of field value


SELECT a.id, LAST(a.vaue), LAST(a.value), LAST(a.refno), COUNT(*)

FROM myTable As a INNER JOIN myTable As b
ON a.vaue = b.vaue
AND (a.value < b.value OR
( a.value = b.value AND
(a.refno < b.refno OR
(a.refno = b.refno AND a.id<=b.id)
)))

GROUP BY a.id





The complex ON clause try to define the "ordering". A record, a, will occur
before another one, b, if
( its value < other record value OR
( their value is equal AND
( ...


The comparison (ordering) occurs only on the records having the same vaue
value.


Hoping it may help,
Vanderghast, Access MVP


ooxx said:
oh oh oh ..

Dear,

Yes, there is a field one named "id" as a primary key. And the original
Table fields :
id as primary vaue as sum of value value property as number and refno :

The sources are:
id vaue value refno
1 4 2 1
2 4 2 1
3 3 3 2
5 3 2 3
6 3 1 3

and to make it ranks by group:

num id vaue value refno
1 1 4 2 1
2 2 4 2 1
1 3 3 3 2
1 5 3 2 3
2 6 3 1 3

.... continued to the last...
Any idea
Thanks



Michel Walsh said:
Hi,


If I understand, you have two identical rows, (4, 1, 1) = (vaue value
refno ); you cannot get different result, num, for each row

vaue value refno num

4 1 1 2
4 1 1 3



as long as, indeed, the rows are absolutely identical. Is there a fourth
field, in addition to (vaue value refno ) that make the row unique?
that
is why a primary key is often quite useful.



Hoping it may help,
Vanderghast, Access MVP



Thanks

Excuse me, I don't expect the 'pk' .

SELECT A.REFNO, Count(*) AS GNO, sum([value]) AS vaue
FROM sample AS A
GROUP BY A.REFNO;

This above SQL is named 'GREFNO'
Then on the 'CollectionFGREFNO' to produce the selection of both
tables
with only the condition of where GREFNO.REFNO = sample.REFNO

to produce the 'CollectionFGREFNO'

SELECT GREFNO.REFNO,GREFNO.vaue, sample.VALUE
FROM GREFNO INNER JOIN sample ON GREFNO.REFNO = sample.REFNO
ORDER BY GREFNO.REFNO;

After that to continue to compare for the counting with the table
'COUNTABLE' with the field 'NUM' counting 1 to 10.

SELECT a.*, b.NUM
FROM CollectionFGREFNO AS a left outer JOIN COUNTABLE as b ON b.NUM
=
a.GNO;

I wish to have them output as :

vaue value refno num
4 2 1 1
4 1 1 2
4 1 1 3
3 3 2 1
8 4 3 1
8 3 3 2
8 1 3 3

That's it.... and continued to the last...
Thanks,



:

Hi,


You can't use * in a GROUP BY query, but


SELECT a.pk, LAST(a.whatever), COUNT(b.num)
FROM CollectionFGREFNO AS a left outer JOIN COUNTABLE as b
ON b.NUM <= a.GNO
GROUP BY a.pk



should count the number of records in b, for each records in a, for
those
records in b that satisfy the comparison.




Hoping it may help,
Vanderghast, Access MVP


he he

Dear Folk,

The problem is when doing the count number like this SQL : example,
the field 'NUM' is the running num from 1 to 10, for example.
SELECT a.*, b.NUM
FROM CollectionFGREFNO AS a left outer JOIN COUNTABLE as b ON b.NUM
=
a.GNO;

or change to :

SELECT a.*, b.NUM
FROM CollectionFGREFNO AS a left outer JOIN COUNTABLE as b ON b.NUM
<=
a.GNO;

For the result is to merge both of the results, there should be no
other
out
flows of records while still results of the same orinal sources as
the
first
SQL.

Thanks
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

INNER JOIN auto changes 6
perform INNER JOIN on query results 2
Query with INNER Join 1
LINQ: Inner Join & Left Outer Join Q 0
An Inner Join on an Inner Join 1
Left Join & Inner Join 2
Library Database 6
Can inner join be done on queries? 8

Back
Top