开发者

MYSQL: how do I remove the first row?

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 re开发者_如何学Cmove 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


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);


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)


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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜