0

I'm having a database with with multiple records where I want to group at. For example:

cat_id | p_id | amount | sold | date       | title
---------------------------------------------
1      | 1    | 4      | 3    | 2015-04-21 | title of product 1
1      | 1    | 0      | 5    | 2015-04-03 | 
2      | 1    | 3      | 1    | 2015-04-21 | title of product 2
2      | 1    | 0      | 3    | 2015-04-05 |

In this table, there are two unique products, p_id 1 in cat_id 1 and p_id 1 in cat_id 2. Now I want to get all a list of all product, sum its amount and sold. The title is only filled in where the amount is > 0. So I wrote this query:

SELECT SUM (amount), SUM(sold) cat_id, p_id, title
FROM table
GROUP BY cat_id, p_id

This returns me: 

cat_id | p_id | amount | sold | title
---------------------------------------------
1      | 1    | 4      | 8    |
2      | 1    | 3      | 4    |

So now I'm missing my title. I tried something link GROUP_CONCAT(DISTINCT title), but that concatenates, and that's not what I'm actually looking for. I just need the title.

How would I fix this.

NOTE: Updated question

Timo002
  • 3,138
  • 4
  • 40
  • 65
  • 2
    Would there ever be a case where the title is different for products of the same cat_id and p_id? If that never happens then why do you even need to group by if you only care about amount > 0? – SLin Apr 21 '15 at 15:30
  • I updated my Question, the question was not complete! There is also a column `sold` that needs to be summed. Even if `amount == 0`. – Timo002 Apr 21 '15 at 15:32

4 Answers4

1

Adding MAX() around title will force it to select the NOT NULL value

SELECT SUM (amount), SUM(sold) cat_id, p_id, MAX(title)
FROM table
WHERE amount > 0
GROUP BY cat_id, p_id
Dan
  • 10,614
  • 5
  • 24
  • 35
1

The underlying issue with the titles here is that if you use GROUP BY without an aggregation function, the value returned is indeterminate (see this question).

So your best bet is to work around this somehow -- an obvious solution would be to fill in the title for each row , not just where the amount is > 0 (or even better, normalize your data!). However, if you can't do that, you can use a separator in GROUP_CONCAT that doesn't exist in your titles (e.g., '||||'), and since GROUP_CONCAT returns a text field, you can use REPLACE to get rid of that:

SELECT SUM (amount), SUM(sold) cat_id, p_id, REPLACE(GROUP_CONCAT(DISTINCT title ORDER BY title DESC SEPARATOR '||||'), '||||', '') AS title
FROM table
GROUP BY cat_id, p_id

I made a simplified SQL Fiddle here that maybe you can play around with to get to do what you want it to.

Community
  • 1
  • 1
Carson Moore
  • 1,287
  • 1
  • 8
  • 9
  • I didn't fill each row with the title because it is redundant information and this saves space. Your solution is working fine, but the `MAX()` function is also working fine and a lot easier. – Timo002 Apr 22 '15 at 06:29
1

You will want to use a MAX() on the title so that it pulls the title that is not blank or null. Seems a little strange though that your table is designed that way...

SELECT SUM(amount), SUM(sold) cat_id, p_id, MAX(title) AS `Title`
FROM table
GROUP BY cat_id, p_id
SLin
  • 375
  • 6
  • 18
  • `MAX()` indeed works. I left the titles out of the other records to save space because it is redundant information. The table indeed is designed a little bit strange. That's because it was never designed for doing what I do now. Think I have to consider redesigning the table. – Timo002 Apr 22 '15 at 06:26
  • @Timo002 You should probably consider using a lookup table for titles if you want to save space from storing title multiple times. – SLin Apr 22 '15 at 13:26
0

Try : 

SELECT SUM (amount), SUM(sold), cat_id, p_id, title
FROM table
GROUP BY cat_id

Update SQLFiddle :http://sqlfiddle.com/#!9/a5ca3/3

Jules
  • 295
  • 1
  • 10
  • Your solution does not work when the first record in the table doesn't have the `titel`, but the second row has the `title`. – Timo002 Apr 22 '15 at 06:31