tatty27 tatty27 - 1 month ago 8
SQL Question

MySQL GROUP unless value equals 0

I would like a query that groups the results together unless the value of job_num = 0 but I have been unable to work out how to do it.

This is my query as it stands (and doesn't work, I get a SQL error)...

SELECT SQL_CALC_FOUND_ROWS * FROM calls
WHERE parent_id = '$term' GROUP BY IF (job_num != 0)
ORDER BY date_start DESC LIMIT $page_position, $item_per_page


I have tried replacing
job_num != 0
with
job_num IS NOT NULL
and get the same result.

The rest of the query works fine until I tried to exclude the
job_num != 0


This a simplified version of the table

id | call_ref | job_num
_______________________

1 | 123445 | 2389
_______________________

2 | 342537 | 0
_______________________

3 | 876483 | 2389
_______________________

4 | 644686 | 5643
_______________________

5 | 654532 | 0


I would like to group the rows where the job_num != 0 but I still want to display the rows where job_num = 0 just not grouped together. The call_ref is not unique and there are a further 31 columns in the table I need the values of.

Using the above example rows with ids 1 and 3 would be grouped and 2,4 and 5 would still return results but not grouped.

Answer

Can you try it with UNION?

(SELECT SQL_CALC_FOUND_ROWS * FROM calls 
WHERE parent_id = '$term' where job_num != 0 GROUP BY (job_num)
ORDER BY date_start DESC LIMIT $page_position, $item_per_page)
UNION
(SELECT SQL_CALC_FOUND_ROWS * FROM calls 
WHERE parent_id = '$term' where job_num = 0
ORDER BY date_start DESC LIMIT $page_position, $item_per_page);