MySQL Sum grouping issue

Question

I have a table containing jobs to be invoiced. Each row contains two columns, 'value' and 'group'. Like this

ID   Value  Group
1   2000.00   1
2   2000.00   1
3   1000.00   0
4   1000.00   0

What I need to do is combine the values in Rows 1 and 2 (because they have the same group number), then return rows 3 and 4 as normal (so, not grouped together):

4000    //Rows 1 and 2 combined
1000    //Row 3 returned as whole value
1000    //Row 4 returned as whole value

I've tried to use GROUP BY in the query, so something like

  SELECT SUM(Value) AS totalValue FROM table GROUP BY Group

However this returns

4000
2000 //Row 3 and 4 combined

It's combining Row 3 and 4 because they share the same Group number, its grouping them together.

My problem is, I don't want them grouped together. I want them to return separately as they have a value of zero. Is there any way for me to do this?

Aziz · Accepted Answer · 2012-03-21 11:28:44Z

1

SELECT Group AS Unique_ID, SUM(Value) AS totalValue FROM table WHERE Group>0 GROUP BY Group
UNION
SELECT id AS Unique_ID, Value As totalValue FROM table WHERE Group=0

edited Mar 21, 2012 at 11:28

answered Mar 21, 2012 at 11:18

Aziz

20.9k8 gold badges68 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user1211577 Over a year ago

Hi Aziz, thanks for the speedy reply. Unfortunately, this is only returning 4000 1000 instead of 4000 1000 1000. Why is this?

Aziz Over a year ago

That's because you need to select another column that will make each row unique ... I have updated the query.

user1211577 Over a year ago

Yeah, I figured that out too! Thanks kindly for your help Aziz :-)

Collectives™ on Stack Overflow

MySQL Sum grouping issue

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related