CODEWITHSUNDEEP
mysqlsql
Imnotapotato
Imnotapotato

Reputation: 5828

How to GROUP BY and sum when column names are the same with SQL?

This is my table column names: 

id_x | id_y | ts | data_a | data_b | data_c

I am running this query: 

SELECT ts, data_a, data_b, data_c from table 
WHERE id_x=4392 
AND id_y IN (2545,2614,3349,4430) 
AND ts BETWEEN '2017-02-01' AND '2017-03-01'

I get a result that looks like this: 

2017-02-01 | 4   | 4  | 0
2017-02-01 | 140 | 30 | 29
2017-02-02 | 4   | 4  | 0
2017-02-02 | 289 | 63 | 60

The ts field is listed more than once. I prefer to have it once and sum it's cells contents.
Is it possible to group the ts (timestamp) column is such way?

wanted result: 

2017-02-01 | 144 | 34  | 29
2017-02-02 | 293 | 67  | 60

Upvotes: 2

Views: 658

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270623

You need to extract the date and aggregate:

SELECT DATE(ts) as ts_date, SUM(data_a) as data_a,
       SUM(data_b) as data_b, SUM(data_c) as data_c 
FROM table 
WHERE id_x = 4392 AND 
      id_y IN (2545, 2614, 3349, 4430) AND
      ts >= '2017-02-01' AND
      ts < '2017-03-01'
GROUP BY DATE(ts) ;

Notes:

  • This extracts the date from the timestamp. Presumably, there is a time component.
  • GROUP BY and SUM() do the processing you wnat.
  • The date conditions in the WHERE clause have been changed so you get all values for February and none for March.

If you want the date range to include the first of March, use:

      ts >= '2017-02-01' AND
      ts < '2017-03-02'

This construct -- with >= and < -- works for both dates and date/times. I strongly recommend that you not use BETWEEN with date/time data types because the results can be misleading.

Upvotes: 3

Fahmi
Fahmi

Reputation: 37483

You need to use sum() aggregation and group by clause

SELECT ts, sum(data_a), sum(data_b), sum(data_c) from table 
WHERE id_x=4392 
AND id_y IN (2545,2614,3349,4430) 
AND ts BETWEEN '2017-02-01' AND '2017-03-01' 
group by ts

Upvotes: 2

Related Questions