asked on

SQL Statement to get monthly order totals

I have a table that contains (among other fields):

orderdate (date)
orderamt (number)
orderid (number)

I need an SQL 2000 statement that will return the number of orders (count) and total (sum) for each month that the table has records for. Any suggestions? Group By doesn't seem to let me use Month() Year()

Thanks!

Aneesh

Select MONTH(orderDate) OrderMonth, SUM(OrderAmt) TotAmt, COUNT(*) TotalOrders
FROM urTable
GROUP BY OrderMonth

ASKER CERTIFIED SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jglazer63

ASKER

Doesn't work in SQL 2000. Actually that was my first thought too.

In addition, I need to deliminate the years as well so I need like totals for FEB06 and FEB07 (02/06 02/07 work too)

opho

Like this?

select
datepart(m, orderdate) as Month,
datepart(yyyy, orderdate) as Year,
count(*) as orderCount,
sum(orderamt) as orderamount
from
dbo.orders
group by
datepart(yyyy, orderdate),
datepart(m, orderdate)
order by
datepart(yyyy, orderdate),
datepart(m, orderdate)

jglazer63

ASKER

Sorry opho. aneeshattingal was just slightly ahead of ya ;-)

Aneesh

Select DATENAME(mm, orderdate) +CAST(RIGHT(YEAR(orderdate),2)AS varchar), SUM(OrderAmt) TotAmt, COUNT(*) TotalOrders
FROM UrTable
GROUP BY DATENAME(mm, orderdate) +CAST(RIGHT(YEAR(orderdate),2)AS varchar)

Aneesh

Select LEFT(DATENAME(mm, orderdate),3) +CAST(RIGHT(YEAR(orderdate),2)AS varchar), SUM(OrderAmt) TotAmt, COUNT(*) TotalOrders
FROM urTable
GROUP BY LEFT(DATENAME(mm, orderdate),3) +CAST(RIGHT(YEAR(orderdate),2)AS varchar)

opho

All's well.. I'm all about collaboration more than competition.

It's midnight on Saturday for me, so I find it a little comical (and totally helpful) that a question can be posted in this timeframe and answered in less than 15 minutes, AND have multiple credible responses.

Experts Exchange ROCKS!

jglazer63

ASKER

Yea me too (find it funny). But hey, what else do us programmers got to do. Geekdum I guess ;-)