CODEWITHSUNDEEP
mysqlsql
David542
David542

Reputation: 110582

Using a GROUP BY statement to sum rows

I have multiple product sales in different markets, and I need to sum up the totals for each title. To start:

mysql> SELECT title, partner_share_currency, us_earnings_usd, cad_earnings_cad 
        FROM raw_financials WHERE title LIKE "%Gamers%";
+--------+------------------------+-----------------+------------------+
| title  | partner_share_currency | us_earnings_usd | cad_earnings_cad |
+--------+------------------------+-----------------+------------------+
| Gamers | USD                    |          3.2500 |           0.0000 |
| Gamers | CAD                    |          0.0000 |           4.0000 |
| Gamers | USD                    |          4.5000 |           0.0000 |
+--------+------------------------+-----------------+------------------+

This is what I currently am doing to get the GROUP BY title:

mysql> SELECT title, us_earnings_usd, cad_earnings_cad 
        FROM raw_financials WHERE title LIKE "%Gamers%" GROUP BY title;
+--------+-----------------+------------------+
| title  | us_earnings_usd | cad_earnings_cad |
+--------+-----------------+------------------+
| Gamers |          3.2500 |           0.0000 |
+--------+-----------------+------------------+

As you can see, it does not sum the value rows. How would I change the SELECT statement such that it sums up the value rows, to give me:

+--------+-----------------+------------------+
| title  | us_earnings_usd | cad_earnings_cad |
+--------+-----------------+------------------+
| Gamers |          7.7500 |           4.0000 |
+--------+-----------------+------------------+

Upvotes: 0

Views: 274

Answers (2)

a_m0d
a_m0d

Reputation: 12205

Try something like:

mysql> SELECT title, SUM(us_earnings_usd) AS us_earnings_usd, SUM(cad_earnings_cad) AS cad_earnings_cad
        FROM raw_financials WHERE title LIKE "%Gamers%" GROUP BY title;

SUM is a function that will operate on each group when you have that GROUP BY clause on your statement. For more similar functions, see MySQL aggregate functions.

Upvotes: 2

Michael Berkowski
Michael Berkowski

Reputation: 270775

You need an aggregate SUM() to sum your rows. You only have a GROUP BY. 

SELECT
  title, 
  partner_share_currency,
  SUM(us_earnings_usd) AS usd,
  SUM(cad_earnings_cad) AS cad
FROM raw_financials 
WHERE title LIKE '%Gamers%'
GROUP BY title, partner_share_currency

Note that MySQL will permit you to include only title in the GROUP BY clause, where most other RDBMS will also require you to list the other non-aggregate columns in GROUP BY. Specifically here, that's partner_share_currency

Upvotes: 3

Related Questions