CODEWITHSUNDEEP
mysqlgroup-by
Ninet9
Ninet9

Reputation: 183

how can i achieve this mysql group query

I have two tables A and B. My goal is to list every row from A, while attach the SUM of 'amount' from B.

like this:

SELECT a.name, 
       SUM(b.amount) as amount 
FROM a 
LEFT JOIN b ON a.id = b.a_id 
GROUP BY a.id

No probleam until this, but i also need to check whether the B table's 'shop_id' matches a value, and if does, i want that single row's 'amount', and not the SUM of all groupped rows. I hope it is understandable. 

Table A  
id     name
----------------
1      john
2      doe
3      smith

Table B
a_id    amount    shop
-----------------------
1         4        1
1         3        2
2         2        2
2         7        3
3         3        3
3         1        2

Desired result with 'shop'=1: 

name   amount    shop
---------------------
john     4         1         //no SUM, only the value of amount where shop=1
doe      9         0         //sum(7,2) because shop is not 1
smith    4         0         //sum(3,1)

I was thinking of an if statement at the SUM() selection something similar to this, but the below statement returns not the desired groupped row value

SELECT a.name,
       ( CASE
           WHEN b.shop <> 1 THEN Sum(b.amount)
           ELSE b.amount
         end ) AS amount,
       ( CASE
           WHEN b.shop <> 1 THEN 0
           ELSE b.shop
         end ) AS shop
FROM   a
       LEFT JOIN b
              ON a.id = b.a_id
GROUP  BY a.id

Any ideas ? Is there a way to put this condition to the GROUP like: case shop<>1 THEN .... ELSE GROUP BY a.id ??

Upvotes: 0

Views: 42

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521629

You were on the right track to use what is called conditional aggregation. Here is a version which should work:

SELECT
    a.name,
    CASE WHEN MAX(CASE WHEN b.shop = 1 THEN 1 ELSE 0 END) > 0
         THEN SUM(CASE WHEN b.shop = 1 THEN b.amount ELSE 0 END)
         ELSE SUM(b.amount) END AS amount,
    MAX(CASE WHEN b.shop = 1 THEN 1 ELSE 0 END) AS shop
FROM a
LEFT JOIN b
    ON a.id = b.a_id
GROUP BY
    a.name;

enter image description here

Demo

Upvotes: 2

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

  • You will need to Left Join twice with the table b.
  • First Join will enable computation of Sum.
  • Second Join will join only when shop = 1. So if we get some non-null value due to second join, we will consider that, else the Sum

Try the following:

SELECT 
  a.id, 
  a.name, 
  COALESCE(MAX(b2.amount), SUM(b1.amount)) AS amount, 
  COALESCE(b2.shop, 0) AS shop 
FROM a 
LEFT JOIN b AS b1 ON a.id = b1.a_id 
LEFT JOIN b AS b2 ON a.id = b2.a_id AND b2.shop = 1
GROUP BY a.id, a.name

Result

| id  | name  | amount | shop |
| --- | ----- | ------ | ---- |
| 1   | john  | 4      | 1    |
| 2   | doe   | 9      | 0    |
| 3   | smith | 4      | 0    |

View on DB Fiddle

Upvotes: 2

Related Questions