jglazer63
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!
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!
Last Comment
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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)
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)
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)
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)
ASKER
Sorry opho. aneeshattingal was just slightly ahead of ya ;-)
Select DATENAME(mm, orderdate) +CAST(RIGHT(YEAR(orderdate
FROM UrTable
GROUP BY DATENAME(mm, orderdate) +CAST(RIGHT(YEAR(orderdate
FROM UrTable
GROUP BY DATENAME(mm, orderdate) +CAST(RIGHT(YEAR(orderdate
Select LEFT(DATENAME(mm, orderdate),3) +CAST(RIGHT(YEAR(orderdate
FROM urTable
GROUP BY LEFT(DATENAME(mm, orderdate),3) +CAST(RIGHT(YEAR(orderdate
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!
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!
ASKER
Yea me too (find it funny). But hey, what else do us programmers got to do. Geekdum I guess ;-)
Microsoft SQL Server
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
FROM urTable
GROUP BY OrderMonth