Power By 专否社区

How to join twice from same table. Group by same column

Go To StackoverFlow.com

-1

I have a litte problem here. I want to join twice from the same table, and group by a common value.
Here is the dataset (from table Voucher): 

Date (dd/mm/yyyy)   Amount
--------------------------
01.01.2010          1.000
15.01.2010          2.000
01.03.2010          3.000
01.03.2010          4.000
01.05.2010          5.000
01.01.2011          1.000
01.02.2011          2.000
01.04.2011          3.000
15.04.2011          4.000
01.05.2011          5.000

The result should be like this: 

Month   Amount 2010   Amount 2011
---------------------------------
1       3.000         1.000
2                     2.000
3       7.000
4                     7.000
5       5.000         5.000

How do I solve this?

2012-04-04 06:06
by hightow
Given your other questions, I'd guess SQL Server. Have you not learnt yet that if you're asking an SQL question, you need to tell us which RDBMS you're using - Damien_The_Unbeliever 2012-04-04 06:22
@DamienTheUnbeliever I'm always improving (I hope) - will do better next time : - hightow 2012-04-04 06:47

5

Something along these lines will work for this case:

SELECT
    DATEPART(month,[Date]) as Month,
    SUM(CASE WHEN DATEPART(year,[Date]) = 2010 THEN Amount END) as [Amount 2010],
    SUM(CASE WHEN DATEPART(year,[Date]) = 2011 THEN Amount END) as [Amount 2011]
FROM
    Voucher
GROUP BY
    DATEPART(month,[Date]) as Month

For other situations, you might want to look into PIVOT.

Date is a really poor name for a column.

2012-04-04 06:25
by Damien_The_Unbeliever
Tablename is Voucher as written in OP, but no biggie :) Will try this out, thank - hightow 2012-04-04 06:33

-2

You have to give the tables different names like in this example:

SELECT Fruit1, F1FruitName = F1.FruitName, F1FruitCost = F1.FruitCost,
Fruit2, F2FruitName = F2.FruitName, F2FruitCost = F2.FruitCost FROM T1
JOIN T2 F1 ON Fruit1 = F1.Fruit_ID
JOIN T2 F2 ON Fruit2 = F2.Fruit_ID

The concrete syntax depends on your database system.

2012-04-04 06:17
by Sandro
Ads