group by not-null values

Question

I have a database:

+--+----+---------+
|id|name|source_id|
+--+----+---------+
|11|aaaa|null
+--+----+---------+
|12|bbbb|1
+--+----+---------+
|13|cccc|1
+--+----+---------+
|14|dddd|null
+--+----+---------+
|15|eeee|2
+--+----+---------+
|16|ffff|2
+--+----+---------+
|17|gggg|2
+--+----+---------+

I'd like to select all entries, with an exception:

• all entries with source_id == somevalue(not null) should be grouped
• last entry with source_id == null should exist afterwards

Here is the result I expect:

+----+--+----+---------+
COUNT|id|name|source_id|
+----+--+----+---------+
.....|11|aaaa|null
+----+--+----+---------+
.2...|13|cccc|1
+----+--+----+---------+
.....|14|dddd|null
+----+--+----+---------+
.3...|17|ffff|2
+----+--+----+---------+

So far I've done this (note: "change_count" = COUNT):

SELECT *, (To_days(date_expires)-TO_DAYS(NOW())) as dayDiff, COUNT(id) AS change_count FROM mytable
GROUP BY source_id 
HAVING dayDiff < 4 
ORDER BY date_created DESC

That's what I'm getting:

+----+--+----+---------+
COUNT|id|name|source_id|
+----+--+----+---------+
.2...|11|aaaa|null
+----+--+----+---------+
.2...|12|bbbb|1
+----+--+----+---------+
.3...|15|eeee|2
+----+--+----+---------+

As you can see the result has 2 problems:

• entries are grouped by first source_id occurrence (should be last(latest))
• nulls are also grouped (shouldn't be grouped)

Is the task that I've described above achievable? How do I do it?

Edit:

SELECT *, COUNT(id) AS change_count FROM 
(SELECT *, (To_days(date_return_due)-TO_DAYS(NOW())) as dayDiff FROM mytable 
WHERE owner_id='1' 
HAVING dayDiff < 100 
ORDER BY date_created DESC) AS newtable
GROUP BY (CASE WHEN source_id IS NULL THEN id ELSE source_id END)
ORDER BY (CASE WHEN source_id IS NULL THEN 1 ELSE 0 END), date_created DESC";

Answer 1

Try this

SELECT *, IFNULL(source_id,UUID()) as unique_source FROM tablename WHERE (conditions) GROUP BY unique_source

Answer 2

I think the following does what you want:

SELECT *, (To_days(date_expires)-TO_DAYS(NOW())) as dayDiff, COUNT(id) AS change_count
FROM mytable
GROUP BY (case when source_id is null then id else source_id end)
HAVING dayDiff < 4 
ORDER BY (case when source_id is null then 1 else 0 end), date_created DESC

It does a conditional group by so the NULL sourceids will not be grouped. It then puts them last using logic in order by.

I didn't understand what you meant by last occurrence. Now I think I do:

SELECT coalesce(s.id, mytable.id) as id,
       max(case when s.maxid is not null and s.maxid = myable.id then mytable.name
                when s.maxid is null then NULL
                else mytable.name
           end) as name,
       (To_days(date_expires)-TO_DAYS(NOW())) as dayDiff, COUNT(id) AS change_count
FROM mytable left outer join
     (select source_id, MAX(id) as maxid
      from mytable
      where source_id is not null
      group by source_id
     ) s
     on mytable.id = s.maxid
GROUP BY (case when source_id is null then id else source_id end)
HAVING dayDiff < 4 
ORDER BY (case when source_id is null then 1 else 0 end), date_created DESC

This joins in the information from the latest record (based on highest id).

Answer 3

Try changing your group by to be:

GROUP BY coalesce(source_id, id)