| group by question [message #2499] |
Sat, 20 July 2002 15:28  |
James
Messages: 120
Registered: June 2000
|
Senior Member |
|
|
Hi,
I am performing a group by query that gets most of the results that I need only I need to also get the total number of rows? Below is the query and table. Thanks in advance
Select Sum(A), COUNT(*), COUNT(distinct(c))
From TableA
Where TABLEA.year IN ('1997','1998')
group by year
YEAR A B C
1997 5 6 1
1997 5 5 2
1998 2 3 1
1998 2 2 2
1999 8 8 8
1999 3 2 7
2000 1 2 1
with results being:
Sum(A) COUNT(*) COUNT(DISTINCT(C)
10 11 2
4 5 2
Is there any way to get the number of rows in the query..ie 4 ?
|
|
|
|
| Re: group by question [message #2506 is a reply to message #2499] |
Mon, 22 July 2002 10:48  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
That query does not provide the results you show (the count(*) column is 2/2, not 11/5), but...
Are you trying to show the number of rows in the table for those two years (4) - on each row? Or on another row (like a summary number)? Please show what the desired output would look like.
|
|
|
|
| Re: group by question [message #2513 is a reply to message #2499] |
Mon, 22 July 2002 16:16 |
James
Messages: 120
Registered: June 2000
|
Senior Member |
|
|
Thank you for your reply,
I am trying to get the number 4 (in addition to the other numbers) - the number of rows in the query for the 2 years.
Thanks
|
|
|
|
| Re: group by question [message #2522 is a reply to message #2499] |
Tue, 23 July 2002 08:32 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Well, you didn't provide a sample of the desired output, but if you just want another row showing the total, then:
select sum(a), count(*), count(distinct(c))
from tablea
where year betwen '1997' and '1998'
group by year
union all
select count(*), null, null
from tablea
where year between '1997' and '1998'; |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
