Q
Problem solve Get help with specific problems with your technologies, process and projects.

Nested selects

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.

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 last published in December 2004

Dig Deeper on SQL Server Database Modeling and Design

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close