Women in Technology

Hear us Roar

Article:		SQL Subqueries
Subject:		subqueries and group by
Date:		2005-09-16 10:21:39
From:		LadyReader

I have a SQL table that contains data accumulated over many months. I need to: 1) extract the rows from with a specified date range 2) consolidate the remaining rows where 2 of the fields both match 3) Sum up the contents of a 3rd field ("Qty") during that consolidation 4) select all fields, both summed and not, of the consolidated rows to import into a 2nd table. The problem I am having is that in order to select all the fields, SQL is insisting that all the fields must appear in the Group By clause I used to sum up Qty. I tried to use a subquery to do the initial date range filter, and this worked fine. However, when I tried to use another subquery to then sum on Qty, SQL threw an error saying that the subquery was returning more tahn 1 result. I understand the problem but I don't know how to overcome it. Can you advise? Thanks!