CODEWITHSUNDEEP
sqlt-sql
MaiOM
MaiOM

Reputation: 936

Join two queries into one in order to join the results

I have a small sql question for you.

I have two queries:

SELECT tbl_CostPrevisions.Month, SUM(tbl_CostPrevisions.Value) AS 'COST' FROM tbl_CostPrevisions
WHERE tbl_CostPrevisions.Year = @YEAR
GROUP BY tbl_CostPrevisions.Month

SELECT tbl_IncomePrevisions.Month, SUM(tbl_IncomePrevisions.Value) AS 'INCOME' FROM tbl_IncomePrevisions
WHERE tbl_IncomePrevisions.Year = @YEAR
GROUP BY tbl_IncomePrevisions.Month

Giving as the result something like:

1   48550,41
2   61082,86
3   49479,63
4   46529,74
5   46549,74
6   48619,63
7   49649,63
8   48279,92
9   48192,42
10  48467,42
11  48201,42
12  48262,42

And

1   8123,16
2   9174,15
3   50009,12
4   50705,00
5   31971,00
6   15217,00
7   13151,00
8   9677,00
9   9616,00
10  9653,00
11  9698,00
12  9605,00

How can I write just one query that will give me the following result?

1   48550,41    8123,16
2   61082,86    9174,15
3   49479,63    50009,12
4   46529,74    50705
5   46549,74    31971
6   48619,63    15217
7   49649,63    13151
8   48279,92    9677
9   48192,42    9616
10  48467,42    9653
11  48201,42    9698
12  48262,42    9605

Thanks

Upvotes: 0

Views: 67

Answers (3)

Richard Nixon
Richard Nixon

Reputation: 169

Try the following query. INNER JOIN on tbl_IncomePrevisions so both lots of data can be used. I have named the tables to make it easier for you.

SELECT cp.Month, SUM(cp.Value) AS 'COST', SUM(ip.Value) AS 'INCOME' 
FROM tbl_CostPrevisions cp
INNER JOIN tbl_IncomePrevisions ip ON ip.Month = cp.Month AND ip.Year = cp.Year
WHERE cp.Year = @YEAR AND ip.Year = @YEAR
GROUP BY cp.Month, ip.Month

Upvotes: 1

Hubert Schölnast
Hubert Schölnast

Reputation: 8497

SELECT
    tbl_CostPrevisions.Month, 
    SUM(tbl_CostPrevisions.Value) AS 'COST',
    SUM(tbl_IncomePrevisions.Value) AS 'INCOME' 
FROM
    tbl_CostPrevisions,
    tbl_IncomePrevisions
WHERE tbl_CostPrevisions.Year = @YEAR
AND   tbl_IncomePrevisions.Year = @YEAR
AND   tbl_CostPrevisions.Month = tbl_IncomePrevisions.Month
GROUP BY 
    tbl_CostPrevisions.Month, 
    tbl_IncomePrevisions.Month

Upvotes: 1

Vikram
Vikram

Reputation: 8333

try the following inner join:

SELECT tbl_CostPrevisions.Month, SUM(tbl_CostPrevisions.Value) AS 'COST' , SUM(tbl_IncomePrevisions.Value) AS 'INCOME'
FROM tbl_CostPrevisions
INNER JOIN tbl_IncomePrevisions on tbl_CostPrevisions.Month = tbl_IncomePrevisions.Month
WHERE tbl_CostPrevisions.Year = @YEAR AND tbl_IncomePrevisions.Year = @YEAR
GROUP BY tbl_CostPrevisions.Month

Upvotes: 2

Related Questions