Question 276017 in Mysql Question posted in
The official MySQL documentation can be found here.

Sum with group by (with case statement) in MySQL query

I am facing one issue in my following Mysql query. I have a table with columns id, pid, state, price1, discount, tot4 and tech1. I try to get a group by sum where pid is same and state value is 11 and 12.

But the issue which I am facing is that current query sums every entry where pid is same an state is anything. I want it should only sum where state is 11 and 12.

SELECT 
    id, pid, state, 
    SUM(CASE WHEN state in (11, 12) THEN price1 ELSE price1 END) as price1,
    SUM(CASE WHEN state in (11, 12) THEN discount ELSE discount END) as discount ,
    SUM(CASE WHEN state in (11, 12) THEN tot4 ELSE tot4 END) as tot4 ,
  tech1
FROM
    iap4 
GROUP BY 
    pid;
Tags:

2

Answers


  1. 0

    Based on this ‘I try to get a group by sum where pid is same and state value is 11 and 12.‘

    If you need to filter to certain states, then sql provides the ‘where’ condition to achieve this:

    SELECT 
pid,
SUM(price1) as price1,
SUM(discount) as discount,
SUM(tot4) as tot4
FROM
    iap4 
WHERE 
    state in (11,22)
GROUP BY 
    pid;

    If you want the state level view also in the grouping:

    SELECT 
    pid,
    state,
    SUM(price1) as price1,
    SUM(discount) as discount,
    SUM(tot4) as tot4
FROM
    iap4 
WHERE 
    State in (11,22)
GROUP BY 
    pid, state;

    You can use ‘sum(case when …)’ but this will be less efficient. Note the correct line should be:

    SUM(CASE WHEN state in (11,2) THEN price1 ELSE 0 END) AS price1

    if you put price1 after the ‘ELSE’ then you will sum price1 when the condition is both true and false.

    Login or Signup to reply.
  2. 0

    It looks like you want to sum the columns price1, discount, and tot4 only when the state is 11 or 12, and group the results by pid. Looking at your query, I think you are summing these columns regardless of the state value. Which is in return giving you the sum of all state.

    SELECT 
    pid, 
    SUM(CASE WHEN state in (11, 12) THEN price1 ELSE 0 END) as price1,
    SUM(CASE WHEN state in (11, 12) THEN discount ELSE 0 END) as discount ,
    SUM(CASE WHEN state in (11, 12) THEN tot4 ELSE 0 END) as tot4 ,
    MAX(tech1) as tech1
FROM
    iap4 
WHERE state in (11, 12)
GROUP BY 
    pid;

    I did change the SUM functions to only consider price1, discount, and tot4 when state is 11 or 12. And the WHERE clause filters out the rows with state 11 or 12 before performing the grouping.

    Modify the query after you understand the issue according to your requirements.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search