September 8, 2005 at 1:14 pm
I am trying to summarize data by customer and product.
I have 2 tables:
Products - Product_ID, Customer_ID, Product_Name
Payment - Product_ID, Payment_Month, Payment_Year, Payment
I am trying to summarize by customer and product and summing the payments by each month and year to get the report:
Customer_ID, Product_Name, Month1_Year1_Payment, Month2_Year_Payment
Here is what I have so far:
SELECT b.customer_id, Product_Name,
(SELECT SUM(payment)
FROM Payment
WHERE [year]= '2005' AND [month] = '1') AS [Month1_Year1_Payment],(SELECT
SUM(payment) FROM Payment
WHERE [year]= '2005' AND [month] = '2') AS [Month1_Year1_Payment]
FROM Products a INNER JOIN Payment b
ON a.Product_id = b.Product_Id
GROUP BY b.customer_id, Product_Name
Not sure if this is close, but it isn't distinguishing the payment to the Product_Name, it is just summing the total payment by month.
I know I'm missing something simple.
September 8, 2005 at 1:38 pm
Have you tried using case statement, This is a cross-tab query
select b.customer_id, Product_Name, [year],
sum(case [month]
when '1'
then payment
end) as [Month1_Year1_Payment],
sum(case [month]
when '2'
then payment
end) as [Month2_Year1_Payment],
FROM Products a INNER JOIN Payment b
ON a.Product_id = b.Product_Id
GROUP BY b.customer_id, Product_Name, Year
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_14_04j7.asp
September 8, 2005 at 1:58 pm
Excellent. That is what it needed.
Thanks for the quick reply.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply