I have an Orders table with persons and toys. Sample values are:
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
person toy 1 puzzle 1 puzzle 1 doll 1 game 2 puzzle 2 doll 3 doll 3 game 3 game 4 doll 4 game 5 doll 5 doll
Persons may have several toys with the same or different names. I'm only interested in persons with games or puzzles. The query results I want from the above table are:
toycount personcount 3 1 2 1 1 2
In English I'd say: Show me a descending count of the number of toys ordered (puzzles and games only) and the number of people who ordered that many. It seems like this should be easy, but I'm just not getting it! Thanks for any help.
The trick here is to do two levels of aggregation. The first level is to count how many of the specified toys (puzzles and games only) were ordered by each person. This gives the "toycount" number for each person. The second level of aggregation is to count how many persons ordered each toycount. Fortunately, it is easy to nest these two levels of aggregation.
select toycount , count(*) as personcount from ( select person , count(*) as toycount from orders where toy in ('puzzle','game') group by person ) as person_toycounts group by toycount order by toycount desc
Dig Deeper on Oracle development languages
Related Q&A from Rudy Limeback
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback.continue reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clausecontinue reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback.continue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.