GROUP BY without all fields? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

GROUP BY without all fields?

Author

Topic

elektrobank
Starting Member

15 Posts

Posted - 2003-05-05 : 02:10:52

Can I use a GROUP BY without having to group on all the fields I'm trying to select?

I have a customer table and a sales table and I query them something like this:

SELECT custDet.id, sales.tot, sum(sales.tot) from custDet, sales
WHERE sales.custID = custDet.id
GROUP BY custDet.id, sales.tot

which would produce something like this

ID | TOT | SUM
~~~~~~~~~~~~~~
1 | 20 | 20
1 | 50 | 50
1 | 15 | 15
4 | 10 | 10
4 | 12 | 12

But what I really want is the SUM to total the sales.tot column for the whole custDet.id group rather than for each line. I know it's doing it per line because I have the sales.tot in the GROUP BY, but I can't remove it so what do I do? What I'm looking for is results like this:

ID | TOT | SUM
~~~~~~~~~~~~~~
1 | 20 | 85
1 | 50 | 85
1 | 15 | 85
4 | 10 | 22
4 | 12 | 22

So the SUM reflects the total sales per group. I need to do it this way because I want to be able to sort the table by the SUM column but still display all sales totals.

Any ideas?

Thanks...

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-05-05 : 02:21:30

You need a subquery.... Not tested or parsed...


SELECT CDid, sales.tot, 
(Select Sum(Tot) from Sales where CustID = CD.ID GROUP BY custID) AS Customertotal
from custDet AS CD, sales 
WHERE sales.custID = CD.id 
GROUP BY CDid, sales.tot

DavidM

"SQL-3 is an abomination.."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources