query

Hi, how to write a query can group customer sale by month? please help.js wrote:
> Hi, how to write a query can group customer sale by month? please
> help.
www.aspfaq.com/5006
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Try,
use northwind
go
select
c.customerid,
year(oh.orderdate) as col_year,
month(oh.orderdate) as col_month,
sum(od.Quantity * (cast((1.00 - od.Discount) as money) * od.UnitPrice))
from
customers as c
inner join
orders as oh
on c.customerid = oh.customerid
inner join
[order details] as od
on oh.orderid = od.orderid
group by
c.customerid,
year(oh.orderdate),
month(oh.orderdate)
order by
c.customerid,
col_year,
col_month
go
-- or
select
c.customerid,
convert(char(6), oh.orderdate, 112) as col_yyyymm,
sum(od.Quantity * (cast((1.00 - od.Discount) as money) * od.UnitPrice))
from
customers as c
inner join
orders as oh
on c.customerid = oh.customerid
inner join
[order details] as od
on oh.orderid = od.orderid
group by
c.customerid,
convert(char(6), oh.orderdate, 112)
order by
c.customerid,
col_yyyymm,
go
AMB
"js" wrote:

> Hi, how to write a query can group customer sale by month? please help.
>
>|||SELECT Count(*), SUM(st.SalesAmount), Month(st.SalesDateField),
Year(st.SalesDateField)
FROM SalesTable st
--WHERE st.CustomerID = 1234
GROUP BY Month(st.SalesDateField), Year(st.SalesDateField)
"js" <js@.someone@.hotmail.com> wrote in message
news:er7mNsxAFHA.3940@.TK2MSFTNGP09.phx.gbl...
> Hi, how to write a query can group customer sale by month? please help.
>
>|||Thanks all,
Can I force it to output as follow(even 1997, Feb doesn't have data):
1997 1 503.5000
1997 2
1997 3 340.0000
1997 4
1997 5 340.0000
1997 6
"David Buchanan" <dbuch328@.cox.net> wrote in message
news:RfyJd.23223$EG1.4463@.lakeread04...
> SELECT Count(*), SUM(st.SalesAmount), Month(st.SalesDateField),
> Year(st.SalesDateField)
> FROM SalesTable st
> --WHERE st.CustomerID = 1234
> GROUP BY Month(st.SalesDateField), Year(st.SalesDateField)
> "js" <js@.someone@.hotmail.com> wrote in message
> news:er7mNsxAFHA.3940@.TK2MSFTNGP09.phx.gbl...
>|||Try,
use northwind
go
select
c.customerid,
p.col_year,
p.col_month,
sum(od.Quantity * (cast((1.00 - od.Discount) as money) * od.UnitPrice)) as
sales
from
customers as c
cross join
(
select
*
from
(
select distinct year(orderdate) from orders
) as y(col_year)
cross join
(
select 1
union all
select 2
union all
select 3
union all
select 4
union all
select 5
union all
select 6
union all
select 7
union all
select 8
union all
select 9
union all
select 10
union all
select 11
union all
select 12
) as m(col_month)
) as p(col_year, col_month)
left join
orders as oh
on c.customerid = oh.customerid and year(oh.orderdate) = p.col_year and
month(orderdate) = p.col_month
left join
[order details] as od
on oh.orderid = od.orderid
where
c.customerid = 'alfki'
group by
c.customerid,
p.col_year,
p.col_month
order by
c.customerid,
p.col_year,
p.col_month
go
AMB
"js" wrote:

> Thanks all,
> Can I force it to output as follow(even 1997, Feb doesn't have data):
> 1997 1 503.5000
> 1997 2
> 1997 3 340.0000
> 1997 4
> 1997 5 340.0000
> 1997 6
>
> "David Buchanan" <dbuch328@.cox.net> wrote in message
> news:RfyJd.23223$EG1.4463@.lakeread04...
>
>|||Thanks Alejandro...
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:706A502C-6657-4F00-BA5F-3B3A4B7E295C@.microsoft.com...
> Try,
> use northwind
> go
> select
> c.customerid,
> p.col_year,
> p.col_month,
> sum(od.Quantity * (cast((1.00 - od.Discount) as money) * od.UnitPrice)) as
> sales
> from
> customers as c
> cross join
> (
> select
> *
> from
> (
> select distinct year(orderdate) from orders
> ) as y(col_year)
> cross join
> (
> select 1
> union all
> select 2
> union all
> select 3
> union all
> select 4
> union all
> select 5
> union all
> select 6
> union all
> select 7
> union all
> select 8
> union all
> select 9
> union all
> select 10
> union all
> select 11
> union all
> select 12
> ) as m(col_month)
> ) as p(col_year, col_month)
> left join
> orders as oh
> on c.customerid = oh.customerid and year(oh.orderdate) = p.col_year and
> month(orderdate) = p.col_month
> left join
> [order details] as od
> on oh.orderid = od.orderid
> where
> c.customerid = 'alfki'
> group by
> c.customerid,
> p.col_year,
> p.col_month
> order by
> c.customerid,
> p.col_year,
> p.col_month
> go
>
> AMB