Add columns to query based on previous queries

Question

I have two tables:

+-----------+
| Customer  |
+-----------+
| ID | Name |
+----+------+
| 1  | Jack |
+----+------+
| 2  | John |
+----+------+

+----------------------------------------+
|                  Bill                  |
+----------------------------------------+
| ID | Customer_ID | date       | amount |
+----+-------------+------------+--------+
| 1  | 1           | 01.01.2015 | 10$    |
+----+-------------+------------+--------+
| 2  | 1           | 01.01.2014 | 20$    |
+----+-------------+------------+--------+
| 3  | 2           | 01.01.2015 | 5$     |
+----+-------------+------------+--------+
| 4  | 2           | 01.02.2015 | 50$    |
+----+-------------+------------+--------+
| 5  | 2           | 01.01.2014 | 15$    |
+----+-------------+------------+--------+

I need to know the sum of all the bills a customer got in a year.

That's pretty easy:

SELECT 
    SUM(Bill.amount), Customer.Name
FROM 
    Customer 
INNER JOIN 
    Bill ON Customer.ID = Bill.Customer_ID
WHERE 
    Bill.date BETWEEN #20150101# AND #20151231#
GROUP BY 
    Customer.Name

The difficult part is that i need to display the results of that query for multiple years in a single table like this:

+-------------------------------------------+
|             sales to customer             |
+-------------------------------------------+
| Customer_ID | Customer_Name | 2015 | 2014 |
+-------------+---------------+------+------+
| 1           | jack          | 10$  | 20$  |
+-------------+---------------+------+------+
| 2           | john          | 55$  | 20$  |
+-------------+---------------+------+------+

I'm using SQL Server 2005.

I'm very grateful for every answer.

sincerly Andahari

that's what we call pivoting the table..you are transposing the table.. — Danyal Sandeelo, Aug 25 '15 at 09:04
Don't store '$' - and definitely don't store it in the same column as the numeric value!!!!!!!!!!! — Strawberry, Aug 25 '15 at 09:09
I don't store dollar. I just added the dollar sign here so that it's clear what is meant by amount. The mysql tag was a mistake sorry — Andahari, Aug 25 '15 at 09:17

score 1 · Answer 1 · answered Aug 25 '15 at 09:05

1

Use a case to only sum values corresponding to your time period. Example:

SELECT sum(case when Bill.date BETWEEN #20150101# AND #20151231# then Bill.amount else 0 end) as 2015, 
Customer.Name
    FROM Customer INNER JOIN Bill ON Customer.ID = Bill.Customer_ID
    GROUP BY Customer.Name

answered Aug 25 '15 at 09:05

AdrianBR

2,762
1
15
29

OP wants to display data for several years Your answer does not correspond to OP's question. – Maciej Los Aug 25 '15 at 09:20
@MaciejLos change the filter? It responds exactly to the OP's question, and gives an example. It also uses sql standard and no subqueries, making it both efficient and compatible with all sql flavors. – AdrianBR Aug 25 '15 at 09:27

score 1 · Accepted Answer · answered Aug 25 '15 at 09:16

1

As stated you need to use a PIVOT in order to achieve the results you are looking for, like this:

Select Customer_ID, Customer_Name, [2015], [2014]
from
(select Customer_ID, Name Customer_Name, YEAR(_date) Yr, amount
from Bill b
inner join Customer c on c.ID = b.Customer_ID
) as src
PIVOT
(SUM(amount) for Yr in ([2015],[2014])) as pvt

answered Aug 25 '15 at 09:16

Christian Barron

2,695
1
14
22

Tag is MySQL, not MS SQL Server. – Maciej Los Aug 25 '15 at 09:18
The MySQL tag was a mistake, he's just said that. It's tagged SQL-SERVER – Christian Barron Aug 25 '15 at 09:19
Ok, i see it now. Your answer deserves for upvote. – Maciej Los Aug 25 '15 at 09:21
I had heard of pivot tables before, but i never knew what they were. I could have saved myself a lot of work in the past... – Andahari Aug 25 '15 at 09:32
If you need more information on them you can look here, you may find a few scenarios where you need to use them: https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx – Christian Barron Aug 25 '15 at 09:34

Add columns to query based on previous queries

2 Answers2