MYSQL: how do I remove the first row?

Question

The first row is my results is all null, how do I remove that from my results?

This is my query:

SELECT COALESCE(UNIX_TIMESTAMP(Date(p.published_at)),0) as 'day', 
       COALESCE(SUM(case when p.status = 2 then p.value end),0) as 'total_accepted',
       COALESCE(SUM(case when p.status = 1 then p.value end),0) as 'total_open',
       COALESCE(SUM(case when p.status = 3 then p.value end),0) as 'total_declined',
       COALESCE(SUM(case when p.status !=0  then p.value end),0) as 'total_published'
FROM posts as p
GROUP BY DATE(p.published_at);

Ive used coalesce to remove any null values from the rest of my result, so the first row is technically all 0's now. but I'm graphing this data, and my lines start all they in 1970... and computers didn't exist back then =p

Answer 1

SELECT COALESCE(UNIX_TIMESTAMP(Date(p.published_at)),0) as 'day', 
       COALESCE(SUM(case when p.status = 2 then p.value end),0) as 'total_accepted',
       COALESCE(SUM(case when p.status = 1 then p.value end),0) as 'total_open',
       COALESCE(SUM(case when p.status = 3 then p.value end),0) as 'total_declined',
       COALESCE(SUM(case when p.status !=0  then p.value end),0) as 'total_published'
FROM posts as p
WHERE 'day' <> 0
GROUP BY DATE(p.published_at)

Just filter out the row you do not need. Another way to do that would be

SELECT COALESCE(UNIX_TIMESTAMP(Date(p.published_at)),0) as 'day', 
       COALESCE(SUM(case when p.status = 2 then p.value end),0) as 'total_accepted',
       COALESCE(SUM(case when p.status = 1 then p.value end),0) as 'total_open',
       COALESCE(SUM(case when p.status = 3 then p.value end),0) as 'total_declined',
       COALESCE(SUM(case when p.status !=0  then p.value end),0) as 'total_published'
FROM posts as p
WHERE p.published_at is not null
GROUP BY DATE(p.published_at)

Answer 2

You can filter out rows containing all NULLs by using a where clause:

WHERE p.published_at IS NOT NULL AND p.status IS NOT NULL and p.value IS NOT NULL

Adding this to the query gives

SELECT COALESCE(UNIX_TIMESTAMP(Date(p.published_at)),0) as 'day', 
       COALESCE(SUM(case when p.status = 2 then p.value end),0) as 'total_accepted',
       COALESCE(SUM(case when p.status = 1 then p.value end),0) as 'total_open',
       COALESCE(SUM(case when p.status = 3 then p.value end),0) as 'total_declined',
       COALESCE(SUM(case when p.status !=0  then p.value end),0) as 'total_published'
FROM posts as p
    WHERE p.published_at IS NOT NULL AND p.status IS NOT NULL and p.value IS NOT NULL
GROUP BY DATE(p.published_at);

If it's just sufficient to filter a NULL date, then using

WHERE p.published_at IS NOT NULL 

is all you need. Once the WHERE clause is in place, there is no need for the COALESCE on the date, since published_at will never be null.

Answer 3

From your description of the problem, this ought to solve it:

SELECT COALESCE(UNIX_TIMESTAMP(Date(p.published_at)),0) as 'day', 
       COALESCE(SUM(case when p.status = 2 then p.value end),0) as 'total_accepted',
       COALESCE(SUM(case when p.status = 1 then p.value end),0) as 'total_open',
       COALESCE(SUM(case when p.status = 3 then p.value end),0) as 'total_declined',
       COALESCE(SUM(case when p.status !=0  then p.value end),0) as 'total_published'
FROM posts as p
WHERE p.published_at IS NOT NULL
GROUP BY DATE(p.published_at);