CODEWITHSUNDEEP
mysqlsql
Dani-Br
Dani-Br

Reputation: 2457

sum when join repeating lines (SQL)

I have query results as follow: 

SELECT ... ON CIA_factbook_dataset.my_name = World_Bank_dataset.my_name ...

+----------------+------+-------------+-----------------+---------+--------+
| my_name        | Year | CIA_name    | World_Bank_name | CIA_GDP | WB_GDP |
+----------------+------+-------------+-----------------+---------+--------+
| United Kingdom | 2010 | UK          | United Kingdom  | 2850    | 2800   |
| United Kingdom | 2010 | UK          | Channel Islands | 2850    |   11   |
| Cyprus         | 2010 | CYPRUS TURK | CYPRUS TURK     |   22    |   22   |
| Cyprus         | 2010 | CYPRUS TURK | CYPRUS GRK      |   22    |   33   |
| Cyprus         | 2010 | CYPRUS GRK  | CYPRUS TURK     |   33    |   22   |
| Cyprus         | 2010 | CYPRUS GRK  | CYPRUS GRK      |   33    |   33   |
+----------------+------+-------------+-----------------+---------+--------+

I need to calculate the sum of the sub-country data, But if I'm using just GROUP BY my_name,year, it calculates the sum of the same number few times.

The final result should be: 

+----------------+------+---------+--------+
| my_name        | Year | CIA_GDP | WB_GDP |
+----------------+------+---------+--------+
| United Kingdom | 2010 | 2850    | 2811   |
| Cyprus         | 2010 |   55    |   55   |
+----------------+------+---------+--------+

Instead of: 

+----------------+------+---------+--------+
| my_name        | Year | CIA_GDP | WB_GDP |
+----------------+------+---------+--------+
| United Kingdom | 2010 | 5700    | 2811   |
| Cyprus         | 2010 |  110    |  110   |
+----------------+------+---------+--------+

How to achieve that?
Any better way than to use SUM(distinct CIA_GDP),SUM(distinct WB_GDP)?
(Theoretically, the GDP of turkish cyprus and greek cyprus might be the same)

Upvotes: 1

Views: 46

Answers (2)

ydoow
ydoow

Reputation: 3006

For this I'm assuming my_name, Year is unique in both tables.

SQL Fiddle

SELECT t1.my_name, t1.YEAR, SUM_CIA_GDP, SUM_WB_GDP
FROM (
    SELECT DISTINCT my_name, YEAR, SUM(CIA_GDP) AS SUM_CIA_GDP
    FROM t
    GROUP BY my_name, YEAR, WB_GDP
    ) t1
JOIN (   
    SELECT DISTINCT my_name, YEAR, SUM(WB_GDP) AS SUM_WB_GDP
    FROM t
    GROUP BY my_name, YEAR, CIA_GDP
    ) t2 
    ON t1.my_name = t2.my_name 
        AND t1.YEAR = t2.YEAR

Results:

|        my_name | YEAR | SUM_CIA_GDP | SUM_WB_GDP |
|----------------|------|-------------|------------|
|         Cyprus | 2010 |          55 |         55 |
| United Kingdom | 2010 |        2850 |       2811 |

Upvotes: 1

Blag
Blag

Reputation: 5894

SQL Fiddle

MySQL 5.6 Schema Setup:

CREATE TABLE t
    (`my_name` varchar(14), `Year` int, `CIA_name` varchar(11), `World_Bank_name` varchar(15), `CIA_GDP` int, `WB_GDP` int)
;

INSERT INTO t
    (`my_name`, `Year`, `CIA_name`, `World_Bank_name`, `CIA_GDP`, `WB_GDP`)
VALUES
    ('United Kingdom', 2010, 'UK', 'United Kingdom', 2850, 2800),
    ('United Kingdom', 2010, 'UK', 'Channel Islands', 2850, 11),
    ('Cyprus', 2010, 'CYPRUS TURK', 'CYPRUS TURK', 22, 22),
    ('Cyprus', 2010, 'CYPRUS TURK', 'CYPRUS GRK', 22, 33),
    ('Cyprus', 2010, 'CYPRUS GRK', 'CYPRUS TURK', 33, 22),
    ('Cyprus', 2010, 'CYPRUS GRK', 'CYPRUS GRK', 33, 33)
;

Query 1:

SELECT my_name, Year, SUM(CIA_GDP), WB_GDP
FROM (
  SELECT my_name, Year, CIA_GDP, SUM(WB_GDP) WB_GDP
  FROM t
  GROUP BY my_name, Year, CIA_GDP
  ) t1
GROUP BY my_name, Year, WB_GDP

Results:

|        my_name | Year | SUM(CIA_GDP) | WB_GDP |
|----------------|------|--------------|--------|
|         Cyprus | 2010 |           55 |     55 |
| United Kingdom | 2010 |         2850 |   2811 |

Upvotes: 2

Related Questions