Group BY
-
Why do you have to use aggregate functions with group by statements?
-
you don't, but it doesn't make sense otherwise. You need something that is "grouped", which is the sum of something, count, etc.
Steve Jones
-
thanks
-
so if you say
select distinct name, count(name)
from table
it won't work
you have to say
select distinct name, count(name)
from table
group by name
and it lists the amount of each instance of the distinct name.
how do you get a total next to each name of all the distinct names?
like if it was 92, 92 next to all the distinct names.
-
I will use:
select name, count(name) as namecount
from tablename
group by name
order by name
I won't add distinct. The reason? if you are grouping by some field, SQL knows that it needs to select only one instance of the name. So the record set will read something like
Name namecount
Allen 92
Richards 15
Watson 46
Good luck!
-
At a previous employer the word DISTINCT in big queries was used as a flag to check the queries for lop sided joins during code reviews.
-
quote:
so if you sayselect distinct name, count(name)
from table
it won't work
you have to say
select distinct name, count(name)
from table
group by name
and it lists the amount of each instance of the distinct name.
how do you get a total next to each name of all the distinct names?
like if it was 92, 92 next to all the distinct names.
Or are you asking if you have
Name namecount
Allen 92
Richards 15
Watson 46
Then you want to see
Name namecount totalcnt
Allen 92 153
Richards 15 153
Watson 46 153
This isn't normal to do but what you are after can be done using WITH ROLLUP like so
select
CASE WHEN (GROUPING(name) = 1) THEN 'Total'
ELSE [name]
END AS [name],
count(name)
from table
group by name WITH ROLLUP
The result will be
Name namecount
Allen 92
Richards 15
Watson 46
Total 153
Then you can use your application to find the Total value and pivot like you want.
-
It was just an opinion on the distinct and the count. I did not pretende to go further than group by name and an extra column in which you have the count per name. I do some checks every now and then for a couple of tables and it has been working fine. Of course the parameters, etc are different, but it can be applied to a different context at any time. The column I use in mine is a PK (no duplicates allowed of course)
-
Sorry cafemar1 I wasn't meaning to make it sound otherwise.
-
Also :-
select name, count(name) as namecount, count(name) * 100 / namecount_total as namecount_percentage
from tablename, (select count(name) as namecount_total
from tablename) name_totals
group by name
order by name
As sometimes it is useful to get the percentage breakdown at the same time. Saves doing it row by row.
-
The SQL parser should use the non-aggregated fields as the default group-by fields. Something for the SQL language committee (whatever its called) to argue about for some years...
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply