total sales each year query help 2

[davyre]

Hi,

I have a table Sales, I want to have a query to display total sales in each year..
The table is like this:

|OrderID|OrderDate|CustomerID|Comment|
|1      |1/1/2011 |12        |       |
|2      |5/1/2011 |13        |       |
...
|600    |1/12/2013|5         |       |

How can I get a query so it will display total order for each year (2011, 2012, 2013)?

Thanks!

 

[SkipVought]

Hi,

Select year(orderdate) as [year], sum([whatever your unstated order amt]) as [Tot Sales]
From [your table name]
Group by year(orderdate)

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[davyre]

Ah, thanks Skip.

This is the code that I use (with slight modification, using COUNT instead of SUM)

SELECT YEAR(TblOrder.OrderDate) AS [Year], COUNT([TblOrder.OrderID]) as [Tot Sales]
FROM [TblOrder]
GROUP BY YEAR(TblOrder.OrderDate) ;

 

[SkipVought]

If you're not summing to get a TOTAL of sales, I'd lable your count as NUMBER of sales, but I'm probably anal retentive.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[davyre]

Another question:

I have this query, but how do I get the top 5 for each year? If I put TOP 5 after SELECT, it will only select top 5 for the whole query. Can I get the top 5 units for each year?

Thanks!

SELECT COUNT(TblCustOrderUnit.UnitID) AS [Number], TblUnits.UnitName, YEAR(TblCustOrder.CustOrderDate) AS [Year]
FROM TblUnits INNER JOIN (TblCustOrder INNER JOIN TblCustOrderUnit ON TblCustOrder.[CustOrderID] = TblCustOrderUnit.[OrderID]) ON TblUnits.[UnitID] = TblCustOrderUnit.[UnitID]
GROUP BY YEAR(TblCustOrder.CustOrderDate), TblUnits.UnitName;

 

[PHV]

One way:

SELECT COUNT(OU.UnitID) AS [Number], U.UnitName, YEAR(O.CustOrderDate) AS [Year] 
FROM TblUnits U INNER JOIN (TblCustOrder O INNER JOIN TblCustOrderUnit OU ON O.CustOrderID = OU.OrderID) ON U.UnitID = OU.UnitID 
GROUP BY YEAR(O.CustOrderDate), U.UnitName
HAVING COUNT(OU.UnitID) IN (SELECT TOP 5 COUNT(*) 
FROM TblCustOrder A INNER JOIN TblCustOrderUnit B ON A.CustOrderID = B.OrderID
WHERE YEAR(A.CustOrderDate) = YEAR(O.CustOrderDate)
GROUP BY B.UnitID ORDER BY 1 DESC)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[davyre]

Great PHV!

But there are two problems here when I tried your code,
First, if there are two records with the same Number, it returns both (so the query result will be like TOP 6 instead of just 5). How can you select either one (doesnt matter for my case)?
Second, what is ORDER BY 1 mean? What is 1? It seems the result was sorted per UnitID, but what I want is to be sorted per Number DESC (i.e largest Number on top for each Year)


Thanks!

 

[PHV]

what is ORDER BY 1 mean?
Sort by first column in the SELECT list.
You may try this:

SELECT COUNT(OU.UnitID) AS [Number], U.UnitName, YEAR(O.CustOrderDate) AS [Year] 
FROM TblUnits U INNER JOIN (TblCustOrder O INNER JOIN TblCustOrderUnit OU ON O.CustOrderID = OU.OrderID) ON U.UnitID = OU.UnitID 
GROUP BY YEAR(O.CustOrderDate), U.UnitName
HAVING [!]Format(COUNT(OU.UnitID),'0000000') & OU.UnitID[/!] IN (SELECT TOP 5 [!]Format(COUNT(*),'0000000') & B.UnitID[/!] 
FROM TblCustOrder A INNER JOIN TblCustOrderUnit B ON A.CustOrderID = B.OrderID
WHERE YEAR(A.CustOrderDate) = YEAR(O.CustOrderDate)
GROUP BY B.UnitID ORDER BY 1 DESC)
[!]ORDER BY 3, 1 DESC[/!]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[davyre]

I can't. It gave me error You tried to execute a query that does not include the specified expression " as part of an aggregate function.

 

[PHV]

Oops, sorry, forgot that:
GROUP BY YEAR(O.CustOrderDate), U.UnitName[!], OU.UnitID[/!]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[davyre]

All good. Thanks PH!