SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: How to Use GROUP BY in SQL Server
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 07/30/2007 :  10:16:43  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote

Summarizing data in a SELECT statement using a GROUP
BY
clause is a very common area of difficulty for beginning SQL programmers. In Part I of this two part series, we'll use a simple schema and a typical report request to cover the effect of JOINS on grouping and aggregate calculations, and how to use COUNT(Distinct) to overcome this. In Part II, we'll finish up our report while examining the problem with SUM(Distinct) and discussing how useful derived tables can be when grouping complicated data.



Read How to Use GROUP BY in SQL Server

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 07/30/2007 :  10:38:24  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Great article!

I hope the solution is far better than using a derived table like this
select customer, sum(itemcount) AS itemcount, sum(orderamount) as orderamount,
count(distinct orderid) as ordercount, sum(totalshipping) as totalshipping
from (
select  
  o.Customer, count(*) as ItemCount, sum(od.Amount) as OrderAmount, 
  o.OrderID, sum(o.ShippingCost) / count(*)  as TotalShipping
from 
  @Orders o
inner join 
  @OrderDetails od on o.OrderID = od.OrderID
group by 
  o.Customer, o.orderid
) as d
group by customer



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 07/30/2007 :  10:57:05  Show Profile  Visit spirit1's Homepage  Reply with Quote
i bet 5 cents on an (sum() over ...) implementation

seriously though, how can something that simple be so hard to understand???

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 07/30/2007 :  11:41:25  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
quote:
Originally posted by Peso

Great article!

I hope the solution is far better than using a derived table like this
select customer, sum(itemcount) AS itemcount, sum(orderamount) as orderamount,
count(distinct orderid) as ordercount, sum(totalshipping) as totalshipping
from (
select  
  o.Customer, count(*) as ItemCount, sum(od.Amount) as OrderAmount, 
  o.OrderID, sum(o.ShippingCost) / count(*)  as TotalShipping
from 
  @Orders o
inner join 
  @OrderDetails od on o.OrderID = od.OrderID
group by 
  o.Customer, o.orderid
) as d
group by customer



E 1205.25"
N 5639.16"



I knew someone would feel compelled try to spoil part II ... oh well. Yes, it is similar to that, but no need for any COUNT(Distinct) or dividing anything by COUNT(*) expressions, it is of course much simpler.

the article is targeted towards beginners, not for sql experts. I tried to really talk about how grouping and summarizing can cause difficulty with duplicates caused by JOINS, something we see people struggle with all the time. Also, I keep noticing that some people use SUM(Distinct) which doesn't really solve their problems, so I mention that in part II as well ... I didn't use any SQL 2005 features because they were a little out of the scope and not necessary, plus I wanted to stick with the basics for beginners as best I could.



- Jeff
http://weblogs.sqlteam.com/JeffS

Edited by - jsmith8858 on 07/30/2007 12:04:41
Go to Top of Page

sqldataguy
Starting Member

12 Posts

Posted - 07/31/2007 :  14:10:26  Show Profile  Reply with Quote
Great article. Looking forward to Part II
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 07/31/2007 :  14:43:12  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Part II found here
http://www.sqlteam.com/article/how-to-use-group-by-with-distinct-aggregates-and-derived-tables



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

bhaaskar
Starting Member

1 Posts

Posted - 08/12/2010 :  09:45:27  Show Profile  Reply with Quote
My first post here: if this has already been answered, kindly point me there. Thank you very much. From the tables Orders and order details tables mentioned in the article (http://www.sqlteam.com/article/how-to-use-group-by-in-sql-server) I would like to get: for each order, the item name of the max priced product in that order.
The result set would be:

orderID customer orderdate shippingCost DetailID Item Amount
----------- ---------- -------------- ------------- --------- ---------- ----------
1 ABC 2007-01-01 40.0000 2 Item B 150.0000
2 ABC 2007-01-02 30.0000 5 Item H 200.0000
3 ABC 2007-01-03 25.0000 6 Item X 100.0000
4 DEF 2007-01-02 10.0000 8 Item Z 300.0000

I am using SQL7.0. SQL2000 solution would work too.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 08/12/2010 :  14:23:40  Show Profile  Reply with Quote
quote:
Originally posted by bhaaskar

My first post here: if this has already been answered, kindly point me there. Thank you very much. From the tables Orders and order details tables mentioned in the article (http://www.sqlteam.com/article/how-to-use-group-by-in-sql-server) I would like to get: for each order, the item name of the max priced product in that order.
The result set would be:

orderID customer orderdate shippingCost DetailID Item Amount
----------- ---------- -------------- ------------- --------- ---------- ----------
1 ABC 2007-01-01 40.0000 2 Item B 150.0000
2 ABC 2007-01-02 30.0000 5 Item H 200.0000
3 ABC 2007-01-03 25.0000 6 Item X 100.0000
4 DEF 2007-01-02 10.0000 8 Item Z 300.0000

I am using SQL7.0. SQL2000 solution would work too.




SELECT o.OrderID,od.Item,od.Amount
FROM Orders o
INNER JOIN OrderDetails od
ON od.OrderID = o.OrderID
INNER JOIN (SELECT OrderID,MAX(Amount) AS MaxAmt
            FROM OrderDetails 
            GROUP BY OrderID)od1
ON od1.OrderID = od.OrderID
AND od1.MaxAmt = od.Amount


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000