Search code examples
sql-serveradventureworks

Join tables then sum on distinct values

I'm working through some Adventureworks Challenges, and I'm trying to whittle down/summarize a joined table.

The following correctly joins my two tables and produces 3 columns:

use AdventureWorks2012
select datename(dw,orderdate ) as "Day", LineTotal,OrderQty
from sales.SalesOrderDetail join sales.SalesOrderHeader
on (SalesOrderDetail.SalesOrderID=SalesOrderHeader.SalesOrderID)

Adjusting it slightly, results in a table that shows only the distinct dates of Monday, tuesday, wednesday, etc...

use AdventureWorks2012
select distinct(datename(dw,orderdate)) as "Day"
from sales.SalesOrderDetail join sales.SalesOrderHeader
on (SalesOrderDetail.SalesOrderID=SalesOrderHeader.SalesOrderID)

enter image description here

I'm trying to figure out how to produce the sum of LineTotal and the sum of OrderQty per day, but not having success.

The closest I've gotten is the following:

use AdventureWorks2012
select distinct(datename(dw,orderdate)) as "Day", sum(LineTotal), SUM(OrderQty)
from sales.SalesOrderDetail join sales.SalesOrderHeader
on (SalesOrderDetail.SalesOrderID=SalesOrderHeader.SalesOrderID)
group by OrderDate

However, this produces many, many rows, instead of just 7 rows and the accompanying totals of LineTotal, OrderQty.

Thanks for any suggestions.

Solution 
  • use AdventureWorks2012
select 
    datename(dw,orderdate ) as "Day", 
    SumLineTotal=SUM(LineTotal),
    SumOrderQty=SUM(OrderQty)
from 
    sales.SalesOrderDetail 
    INNER  join sales.SalesOrderHeader on (SalesOrderDetail.SalesOrderID=SalesOrderHeader.SalesOrderID)
group by 
    datename(dw,orderdate)