We help IT Professionals succeed at work.

Group and sum query

Hi,

I have a couple of tables looking like this:
orders
orderID int
paymentTypeID int
sentDate datetime
orderstatusID int

orderDetail
orderID int
price money
quantity int

bklPaymentType
paymentTypeID int
paymentType nvarchar

I want the result to be like this:
paymenttype    totalsum  
Invoice             50000
Mastercard     25000
Visa               50000

This should be for all transactions in a certain month and year that is based on sentDate that holds when the order was sent. Also, only the orders with orderStatusID = 3 should be shown.

How can I achieve this?

Thanks for help!

Peter
Comment
Watch Question

Peter NordbergIT Manager

Author

Commented:
Hi,

I forgot that I would need to have the month and year in the query result like this:

paymenttype    totalsum    month     year
Invoice             50000        october  2011
Mastercard     25000         october  2011
Visa               50000          october  2011
Senior Developer Analyst
Commented:
See attached code.  The trick is to use a case statement to filter SUM aggregates based on paymenttype.
declare @Orders table (orderID int
,	paymentTypeID int
,	sentDate datetime
,	orderstatusID int)

declare @OrderDetail table (
	orderID int
,	price money
,	quantity int)

declare @bklPaymentType table (
	paymentTypeID int
,	paymentType nvarchar(50))


insert into @Orders values (1, 1, '11/1/2011', 1)	
insert into @Orders values (2, 2, '11/1/2011', 1)
insert into @Orders values (3, 2, '10/31/2011', 2)

insert into @OrderDetail values (1, 12.00, 3)
insert into @OrderDetail values (2, 32.00, 3)
insert into @OrderDetail values (2, 42.00, 3)
insert into @OrderDetail values (3, 92.00, 2)

insert into @bklPaymentType values (1, 'MasterCard')
insert into @bklPaymentType values (2, 'Visa')


select	
		SUM(price)  as Invoice
	,	SUM(case when paymentType = 'MasterCard' then price else 0 end) as MasterCard
	,	SUM(case when paymentType = 'Visa' then price else 0 end)  as Visa
	,	month(SentDate) as Invoice_Month
	,	year(sentdate) as Invoice_Year
	
from	@orders orders

inner join @orderDetail orderDetails on
	orders.OrderId = OrderDetails.OrderId
	
inner join @bklPaymentType PaymentTypes on 
	PaymentTypes.paymentTypeId = orders.PaymentTypeId
	
where orderstatusID <> 3

group by year(sentdate), month(SentDate)

Open in new window

lcohanDatabase Analyst
CERTIFIED EXPERT

Commented:
You mean something like this:


select pt.paymentType, sum(od.price), datepart(month,sentDate),datepart(year,sentDate)
from orders o
            inner join orderdetail od on o.orderid = od.orderid
            inner join bklPaymentType pt on o.paymentTypeID = pt.paymentTypeID
where o.orderStatusID = 3
group by 1,3,4

Explore More ContentExplore courses, solutions, and other research materials related to this topic.