CODEWITHSUNDEEP
mysqlsql
Moyshe Zuchmir
Moyshe Zuchmir

Reputation: 1642

Group records by two columns

I have table named "invoices". I would like to sum the amount and grouped it by companies and date

+------------+------------------+-------------+-----------+
| company_id | company          | date        | amount    |
+------------+--------------------------------+-----------+
|    1       | chevrolet        | 2017-11-18  |   100     |
|    1       | chevrolet        | 2017-11-18  |   -70     |
|    1       | chevrolet        | 2017-11-25  |   50      |
|    2       | mercedes         | 2017-04-01  |   30      |
|    2       | mercedes         | 2017-04-01  |   -30     |
|    2       | mercedes         | 2017-09-01  |   50      |
|    3       | toyota           | 2017-05-12  |   60      |
+------------+------------------+-------------+-----------+

The desired result is:

+------------+------------------+-------------+-----------+
| company_id | model_name       | date        | amount    |
+------------+--------------------------------+-----------+
|    1       | chevrolet        | 2017-11-18  |   30      |
|    1       | chevrolet        | 2017-11-25  |   50      |
|    2       | mercedes         | 2017-04-01  |   0       |
|    2       | mercedes         | 2017-09-01  |   50      |
|    3       | toyota           | 2017-05-12  |   60      |
+------------+------------------+-------------+-----------+

How can I do it?

Upvotes: 0

Views: 37

Answers (3)

cse
cse

Reputation: 4104

Use following: 

SELECT company_id, model_name, date , SUM(amount) AS amount 
             FROM invoices GROUP BY company, date;

See here and here for more about GROUP BY clause and examples.

Upvotes: 0

AurA
AurA

Reputation: 12373

Together

SELECT 
SUM (amount) 
FROM <table-name> 
GROUP BY company, date;

For grouping by company

SELECT 
SUM (amount) 
FROM <table-name> 
GROUP BY company;

For grouping by date

SELECT 
SUM (amount) 
FROM <table-name> 
GROUP BY date;

Upvotes: 0

Caius Jard
Caius Jard

Reputation: 74660

You already have the spec in english there, it just needs translating to SQL:

select company_id, model_name, date, sum(amount) as amount
from invoices
group by company_id, model_name, date

In MySQL you can (depending on how it's configured) get away without doing the GROUP BY line, and you might see SQLs like this on your travels through the world of MySQL:

select company_id, model_name, date, sum(amount) as amount
from invoices

MySQL is implicitly inserting the group by for you.. Personally I'd always recommend to put it in explicitly, as few other DBs do an "auto group by" and sticking to standard SQL makes your SQL knowledge more portable. You might also find strong proponents of the "group by should always be implicit" argument which, I acknowledge, has its merits :)

Upvotes: 1

Related Questions