April 24, 2015 at 10:03 am
Hi,
I need to list customers in a table that represents sales over the years.
I have tables:
Customers -> id | name |...
Orders -> id | idCustomer | date | ...
Products -> id | idOrder | unitprice | quantity | ...
I am using this SQL but it only gets one year:
SELECT customers.name , SUM(unitprice*qt) AS total
FROM Products
INNER JOIN Orders ON Orders.id = Products.idOrder
INNER JOIN Customers ON Customers.id = Orders.idCustomer
WHERE year(date)=2014
GROUP BY customers.name
ORDER BY 2 DESC
I need something like this:
customer | total sales 204 | total sales | 2015 | total sales (2014 + 2015)
--------
customer A | 1000$ | 2000$ | 3000$
customer B | 100$ | 100$ | 200$
Is it possible to retrieve these values in a single SQL query for multiple years and grand total?
Thanks!
April 24, 2015 at 10:52 am
You have a WHERE clause that limits to 2014. You'll want to group by years to get the values by years.
However, if you want to move them into columns, you'll need a PIVOT (not recommended) or a crosstab. This might help: http://www.sqlservercentral.com/articles/Crosstab/65048/
April 24, 2015 at 11:18 am
Thanks for the info.
Is there a way to do something like this (without the year limitation in the WHERE clause):
SELECT customers.name,
(
SELECT (SUM(unitprice*qt) AS total
FROM Products
INNER JOIN Orders ON Orders.id = Products.idOrder
INNER JOIN Customers ON Customers.id = Orders.idCustomer
WHERE year(date)=2014
) AS total_2014,
(
SELECT (SUM(unitprice*qt) AS total
FROM Products
INNER JOIN Orders ON Orders.id = Products.idOrder
INNER JOIN Customers ON Customers.id = Orders.idCustomer
WHERE year(date)=2015
) AS total_2015,
FROM Products
INNER JOIN Orders ON Orders.id = Products.idOrder
INNER JOIN Customers ON Customers.id = Orders.idCustomer
GROUP BY customers.name
April 24, 2015 at 11:58 am
I'd crosstab it more like this:
SELECT customers.name,
SUM( case when year(date) = 2014 then unitprice*qt else 0 end) AS total_2014,
SUM( case when year(date) = 2015 then unitprice*qt else 0 end) AS total_2015,
FROM Products
INNER JOIN Orders ON Orders.id = Products.idOrder
INNER JOIN Customers ON Customers.id = Orders.idCustomer
GROUP BY customers.name
April 24, 2015 at 2:54 pm
Steve Jones - SSC Editor (4/24/2015)
I'd crosstab it more like this:
SELECT customers.name,
SUM( case when year(date) = 2014 then unitprice*qt else 0 end) AS total_2014,
SUM( case when year(date) = 2015 then unitprice*qt else 0 end) AS total_2015,
FROM Products
INNER JOIN Orders ON Orders.id = Products.idOrder
INNER JOIN Customers ON Customers.id = Orders.idCustomer
GROUP BY customers.name
or if you want to be more generic:
SELECT customers.name,
SUM( case when year(date) = year(get_date())-1 then unitprice*qt else 0 end) AS total_PreviousYear,
SUM( case when year(date) = year(get_date()) then unitprice*qt else 0 end) AS total_CurrentYear,
FROM Products
INNER JOIN Orders ON Orders.id = Products.idOrder
INNER JOIN Customers ON Customers.id = Orders.idCustomer
GROUP BY customers.name
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 27, 2015 at 2:53 am
It worked great!
Thanks for the replies.
I was wondering if its possible to list only customers who made purchases in both years.
I mean i get a lot of these:
Customers | 2014 | 2015
Customer 1 | 0.00 | 0.00
Customer 2 | 0.00 | 0.00
Customer 3 | 0.00 | 0.00
How can i show only customers with sales > 0 ?
Thanks once again.
April 27, 2015 at 9:44 am
define sales > 0. You have multiple years.
You can add a HAVING clause to the end. Read up on that.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply