Manish Sapkal Manish Sapkal - 1 month ago 10
SQL Question

Mysql - Grouping on varchar field ignoring NULL value

I have records (Sample Data) as follows in one of my table in MySql.

Date BlogID Posts Followers Follower_Name
==== ====== ===== ========= =============
2016-10-12 1 2 0 NULL
2016-10-12 1 0 1 Steave
2016-10-11 1 1 0 NULL
2016-10-11 1 0 3 Adolf, Ainsley, Aldwin


Now I want date and blogid wise grouped data. and want result as follows

Date BlogID Posts Followers Follower_Name
==== ====== ===== ========= =============
2016-10-12 1 2 1 Steave
2016-10-11 1 1 3 Adolf, Ainsley, Aldwin


Means sum of Posts and followers group by Date & BlogID and ignore null in Follower_Name field and merge with Date and BlogID.

Can anyone give me some hint?

Thanks

Answer

Try something like this

SELECT Date,
       BlogID,
       Sum(Posts),
       Sum (Followers),
       Max(Follower_Name)
FROM   Yourtable
GROUP  BY Date,
          BlogID 

Considering the Follower_Name will have data only once per Date,BlogID combination. If you have more than one Follower_Name for Date,BlogID combination then you can use GROUP_CONCAT to concatenate the names

SELECT Date,
       BlogID,
       Sum(Posts),
       Sum (Followers),
       GROUP_CONCAT(Follower_Name)
FROM   Yourtable
GROUP  BY Date,
          BlogID 

Note : Storing comma separated names in Follower_Name column is not a good idea consider changing your table structure