Aggregate with Multiple fields question

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

General SQL Server Forums

New to SQL Server Programming

Aggregate with Multiple fields question

Author

Topic

newsqluser123
Starting Member

7 Posts

Posted - 2008-05-07 : 15:44:36

I'm having trouble using Aggregate functions with multiple fields.
Here is what I'm trying to do:

SELECT REPLACE(Cust_Key, sum(PRODUCT_CHARGE), count(Tracking_Number), Tracking_Number FROM Shipments = '2008-05-05'
Group By Cust_Key, Tracking_Number

I can get this to work when I use Group By for a single field like Cust_Key but I don't know how to make it work for multiple fields. What is the best way to write something that contains a few aggregate functions on top of multiple fields?

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-07 : 15:51:45

Post a data example of what you mean.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

newsqluser123
Starting Member

7 Posts

Posted - 2008-05-07 : 16:20:09

Thanks for taking a look at my post.
Here's what I have for data

Sale # -Total -Count- Tracking #
-------------------------------------------------
227659 3.16 1 1234567890
228723 3.52 1 1234567891
228789 3.52 1 1234567892
229046 6.49 1 1234567893
229046 10.21 1 1234567894

Here's what I'd like to generate

Sale # -Total -Count- Tracking #
-------------------------------------------------
227659 3.16 1 1234567890
228723 3.52 1 1234567891
228789 3.52 1 1234567892
229046 16.7 2 1234567893
229046 16.7 2 1234567894

I'd have the Sum of total and Ship listed for each instance like it is listed for 229046

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-07 : 16:26:15

This should do it:


SELECT t1.Sale#, t1.SumProductCharge, t1.CountTrackingNumber, t1.TrackingNumber
FROM Table1 t1
INNER JOIN
(
	SELECT Sale#, SUM(ProductCharge) AS SumProductCharge, COUNT(TrackingNumber) AS CountTrackingNumber
	FROM Table1
	GROUP BY Sale#
) dt
ON t1.Sale# = dt.Sale#

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

newsqluser123
Starting Member

7 Posts

Posted - 2008-05-08 : 10:27:30

That did the trick. Thanks for the Help Tara.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources