Group by value which can be found in other rows as well

Question

From another query (which has order by A asc, B asc) I get the following table

A  B  nameA   nameB       date       unique_id      price
=========================================================
1  1  aaaaa   aaaaa    2018-02-01        1              1
1  1  aaaaa   aaaaa    2018-02-01        2             10
1  1  aaaaa   aaaaa    2018-02-02        3              2
1  1  aaaaa   aaaaa    2018-02-02        24            20
1  1  aaaaa   aaaaa    2018-02-03        34             3
1  1  aaaaa   aaaaa    2018-02-04        44             4
1  1  aaaaa   aaaaa    2018-02-04        54            40
1  1  aaaaa   aaaaa    2018-02-04        4            400
1  2  aaaaa   bbbbb    2018-02-01        5              1
1  2  aaaaa   bbbbb    2018-02-02        76             2
1  2  aaaaa   bbbbb    2018-02-02        85            20
1  2  aaaaa   bbbbb    2018-02-02        6            200
1  2  aaaaa   bbbbb    2018-02-03        99             3
---------------------------------------------------------
1  3  aaaaa   ccccc    2018-02-01        7             21
1  4  aaaaa   ddddd    2018-02-01        8             21
1  4  aaaaa   ddddd    2018-02-01        9             11
2  1  bbbbb   aaaaa    2018-02-01        10             1
2  2  bbbbb   bbbbb    2018-02-01        11            20
2  3  bbbbb   ccccc    2018-02-01        12            29
2  3  bbbbb   ccccc    2018-02-01        13            40
3  2  ccccc   bbbbb    2018-02-01        14            31
3  2  ccccc   bbbbb    2018-02-01        15            13
3  9  ccccc   xxxxx    2018-02-01        16            31
3  9  ccccc   xxxxx    2018-02-01        17            10
4  3  ddddd   ccccc    2018-02-01        18            10
4  3  ddddd   ccccc    2018-02-01        19            10
4  4  ddddd   ddddd    2018-02-01        20            10
4  6  ddddd   fffff    2018-02-01        21            10 
...

A and B are ids.

I would like to group by date "per each B id", then sum prices, meaning that each B will have multiple unique dates, (but unique only within B rows). The result would look like

A  B  nameA   nameB       date         total
============================================
1  1  aaaaa   aaaaa    2018-02-01         11
1  1  aaaaa   aaaaa    2018-02-02         22
1  1  aaaaa   aaaaa    2018-02-03          3
1  1  aaaaa   aaaaa    2018-02-04        444
1  2  aaaaa   bbbbb    2018-02-01          1
1  2  aaaaa   bbbbb    2018-02-02        222
1  3  aaaaa   ccccc    2018-02-03          3
...

The problem is that the same date can appear in other rows coming below, but I don't want them grouped all together, so simple SELECT * FROM table GROUP BY date is not working. I want to group dates only for each chuck of rows (having the same B id).

Is this even possible in mysql (or sql) ?

You typically GROUP BY the columns you select, except those who are arguments to set functions. I.e. in your case do `GROUP BY A, B, nameA, nameB, date` — jarlh, Mar 22 '18 at 10:26
so when I put multiple arguments in `group by`, it considers them in the way that I would like them to be? (to group only inside the "chunks" or rows) ? — Lemures, Mar 22 '18 at 10:45

score 0 · Answer 1 · answered Mar 22 '18 at 10:26

0

You can group by using two columns both B and date

SELECT A, B, nameA, nameB, date, SUM(price) as total FROM table GROUP BY date, B

This will group your record by both date and B.

answered Mar 22 '18 at 10:26

Abhilash Ravindran C K

1,818
2
13
22

Will raise an error in newer MySQL versions (unless in compatibility mode.) – jarlh Mar 22 '18 at 10:30
As the first answer, I'll have to accept this. Actually I tried this and somehow it didn't work correctly, but now it's fine. Means I had other bug. – Lemures Mar 22 '18 at 10:46
@jarlh Where would the error be in the new versions? Not allowing multiple GROUP BY columns? – Lemures Mar 22 '18 at 10:47
@Lemures, https://stackoverflow.com/questions/34115174/error-related-to-only-full-group-by-when-executing-a-query-in-mysql – jarlh Mar 22 '18 at 10:48
@Lemures Not giving your expected answer. Have a look at this link: http://sqlfiddle.com/#!9/d3d579/13. It's giving `Total = 12` for `A=1` and `B=1` and `date = 2018-02-01`. – Nishant Gupta Mar 22 '18 at 10:56
@NishantGupta You are right. Now if you also explain why, it would be perfect. – Lemures Mar 22 '18 at 11:06
@Lemures based on your question's part 'I would like to group by date "per each B id", then sum prices' . Above query working fine. as you have 3 entries for B=1 and date = 2018-02-01, Total will be 12. Please have a luk on your datas. – Abhilash Ravindran C K Mar 22 '18 at 11:21
@AbhilashRavindranCK I edited the question so it is clearer. If you see the output example, you'll understand better what I meant. Thanks. – Lemures Mar 22 '18 at 11:43

Nishant Gupta · Accepted Answer · 2018-03-22T11:53:45.143

0

Solution for your problem:

SELECT A,B,nameA,nameB,date,Sum(Price) AS Total 
FROM table1 GROUP BY date,B,A
ORDER BY A,B

Output:

|A  |B  |nameA  |nameB  |   date    |Total|
-------------------------------------------
|1  |1  |aaaaa  |aaaaa  |2018-02-01 |11   |
|1  |1  |aaaaa  |aaaaa  |2018-02-02 |22   |
|1  |1  |aaaaa  |aaaaa  |2018-02-03 |3    |
|1  |1  |aaaaa  |aaaaa  |2018-02-04 |444  |
|1  |2  |aaaaa  |bbbbb  |2018-02-01 |1    |
|1  |2  |aaaaa  |bbbbb  |2018-02-02 |222  |
|1  |2  |aaaaa  |bbbbb  |2018-02-03 |3    |
|1  |3  |aaaaa  |ccccc  |2018-02-01 |21   |
|1  |4  |aaaaa  |ddddd  |2018-02-01 |32   |
|2  |1  |bbbbb  |aaaaa  |2018-02-01 |1    |
|2  |2  |bbbbb  |bbbbb  |2018-02-01 |20   |
|2  |3  |bbbbb  |ccccc  |2018-02-01 |69   |
|3  |2  |ccccc  |bbbbb  |2018-02-01 |44   |
|3  |9  |ccccc  |xxxxx  |2018-02-01 |41   |
|4  |3  |ddddd  |ccccc  |2018-02-01 |20   |
|4  |4  |ddddd  |ddddd  |2018-02-01 |10   |
|4  |6  |ddddd  |fffff  |2018-02-01 |10   |
-------------------------------------------

Follow the link for demo:

http://sqlfiddle.com/#!9/d3d579/14

Answer For Newer MySQL Version:

SELECT tbl2.A,tbl2.B,tbl2.nameA,tbl2.nameB,tbl1.date,tbl1.Total
FROM (
       SELECT A,B,date,Sum(Price) AS Total 
       FROM table1 
       GROUP BY date,B,A
     ) AS tbl1
INNER JOIN (
            SELECT A,B,nameA,nameB
            FROM table1 
            GROUP BY A,B,nameA,nameB
            ) AS tbl2
ON tbl1.A = tbl2.A
AND tbl1.B = tbl2.B
ORDER BY A,B,date

Also, find the demo link:

http://sqlfiddle.com/#!9/d3d579/26

edited Mar 22 '18 at 11:53

answered Mar 22 '18 at 10:30

Nishant Gupta

3,533
1
11
18

Will raise an error in newer MySQL versions (unless in compatibility mode.) – jarlh Mar 22 '18 at 10:38
Something like _Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'support_desk.mod_users_groups.group_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by_. – jarlh Mar 22 '18 at 10:41
I tried it in MySQL 5.6 and it's working but I don't know about MySQL 5.7 @jarlh – Nishant Gupta Mar 22 '18 at 10:43
@Lemures Try this solution. By using only `date` and `B` in `group by` Clause you will not get the desired output – Nishant Gupta Mar 22 '18 at 10:48
... This is weird. With my data, `date,B` and `date,B,A` has same output. In here it looks it gives different output. @jarlh Could you be kind and take a look? [data,B](http://sqlfiddle.com/#!9/d3d579/13), and [data,B,A](http://sqlfiddle.com/#!9/d3d579/9) – Lemures Mar 22 '18 at 11:01
The general GROUP BY rule says: "If a GROUP BY clause is specified, each column reference in the SELECT list must either identify a grouping column or be the argument of a set function." Follow this rule, and you will get correct result, no matter of MySQL version. – jarlh Mar 22 '18 at 11:03
@jarlh Try the accepted answer: http://sqlfiddle.com/#!9/d3d579/20. Which doesn't use column `A` in `GROUP BY` – Nishant Gupta Mar 22 '18 at 11:05
Still a bad idea to write code that wont execute with newer MySQLversions. – jarlh Mar 22 '18 at 11:06
@jarlh Is it okay for newer version now? – Nishant Gupta Mar 22 '18 at 11:12
_The general GROUP BY rule says: "If a GROUP BY clause is specified, each column reference in the SELECT list must either identify a grouping column or be the argument of a set function."_, i.e. `GROUP BY A, B, nameA, nameB, date`. – jarlh Mar 22 '18 at 11:30
@jarlhThat's why I answered again in 'Answer For Newer MySQL Version'. – Nishant Gupta Mar 22 '18 at 11:54

Group by value which can be found in other rows as well

2 Answers2