1

Topic: To group except null values

I welcome participants of a forum!
I can not understand logic of following request. We have the table:
id | value
1 | 2
2 | 3
3 | 2
4 | NULL
5 | NULL
6 | NULL
It is necessary to obtain such data on an output:
id | value
1 | 2
2 | 3
4 | NULL
5 | NULL
6 | NULL
More shortly to group a line in which a field value not null.
Everywhere (in particular on stackoverflow.com) recommend in the end of request - group by ifnull (value, id). Then through the operator unfamiliar to me earlier - group by coalesce (value, id), but that essence.
As a result the request groups on value, but lines with null values disappear.
Even tried - group by if (isnull (value), id, value), result such.

2

Re: To group except null values

If to be more exact that here still an example:
Request:

select * from test;

Output:
"id" "album_id" "album_name"
"1" "22" "test1"
"2" "4" "test2"
"3" "22" "test3"
"9" NULL "test4"
"10" NULL "test5"
Request:

select id, album_id, album_name, if (isnull (album_id), 0, count (*)) as cnt from test
group by ifnull (album_id, id);

"id" "album_id" "album_name" "cnt"
"2" "4" "test2" "1"
"9" NULL "test4" "0"
"10" NULL "test5" "0"
"1" "22" "test1" "2"
Apparently the request returned that that is necessary, but I need to do too most with the table which result of numerous filters and , and the structure of this table is similar test which is presented in this post.
I can not understand where an error

3

Re: To group except null values

GROUP BY COALESCE (album_id, RAND ()) album_id

4

Re: To group except null values

Well and accordingly

SUM (IF (ISNULL (album_id), 0, 1))

5

Re: To group except null values

Akina wrote:

GROUP BY COALESCE (album_id, RAND ()) album_id

Can you meant here it:

GROUP BY COALESCE (album_id, id), album_id

Because this request correctly selects both from the test table and in target, I thank for !

6

Re: To group except null values

ambasador wrote:

Can you meant here it

I meant strictly that wrote. It is not necessary to try to finish speaking for me - all the same it does not turn out.

ambasador wrote:

this request correctly selects both from the test table and in target

In your special case - it works, because ID is unique. But if in the table there is no unique field (or it has inconvenient type) such approach leads to jambs, and even problems.

7

Re: To group except null values

Akina wrote:

I meant strictly that wrote.

It is direct  strictly? If it is strict

GROUP BY COALESCE (album_id, RAND ()) album_id

Then it Syntax Error. Here therefore I specify, and can you RAND () instead of id by mistake blurted out.
Next time, be simply attentive!

8

Re: To group except null values

ambasador wrote:

then it Syntax Error.

I checked up the code, before . Any Syntax Error, the result corresponds to the demanded.

9

Re: To group except null values

Akina wrote:

it is passed...
I checked up the code, before . Any Syntax Error, the result corresponds to the demanded.

Yes lines-poberi, I about a comma after coalesce

10

Re: To group except null values

ambasador , About! An eye-diamond... Yes, persuaded, the comma there is indeed necessary.