I am trying to get a count on an aggregated field for each distinct value.
select sum(amount) from mytable
Then I want to get a count on each value, e.g. how many that were 0.00 or 1.00 etc.
Requires Free Membership to View
The problem, as stated, doesn't really make sense, because the given query will return only one row, containing the overall sum for the entire table. So I will change it slightly. Let's say we're talking about orders placed against items in an online web store. Each order can be for one or more items, and within an order, the various items all have sales amounts. Now let's sum up the sales amounts of all the items on each individual order.
select order_id , sum(amount) as sumamount from mytable group by order_id
So we might have one order for $9.37, two orders for $12.34, one order for $23.45, four orders for $45.67, and so on. In order to obtain this analysis of how many orders were for each different sumamount, we'll use a derived table
select sumamount , count(*) as occurrences from ( select order_id , sum(amount) as sumamount from mytable group by order_id ) as dt group by sumamount
Note that some databases like Microsoft SQL Server will require that you actually name the derived table, so I always give it the name dt. Naming the derived table is also necessary if you wish to join it to other tables in the query.
This was first published in December 2004