MySQL Group by and Fill Empty Rows

MySQL: GROUP BY and fill empty cells with value from next row from this group

Isn't this simply:

select
  max(category1) as category1,
  max(category2) as category2,
  product,
  option,
  count(*)
from mytable
group by product, option;

With an aggregation (GROUP BY) you must always specify how to aggregate the other values you are selecting (e.g. whether you want the maximum or the minimum value etc.). Both MAX and MIN ignore nulls, so you get the value you are interested in.

MySQL GROUP BY and Fill Empty Rows

In this answer I will outline how to generate your calendar tables.

Create three tables for days, hours and minutes:

CREATE TABLE days (
  day DATE,
  PRIMARY KEY (day)
) 
CREATE TABLE hours (
  hour INT,
  PRIMARY KEY (hour)
) 
CREATE TABLE minutes (
  minute INT,
  PRIMARY KEY (minute)
) 

Fill the hours table with the numbers from 0 to 23 and the minutes table with the numbers from 0 to 59. To fill the days table, you can create a procedure like the following:

CREATE PROCEDURE make_days(IN start_date DATE, IN end_date DATE)
BEGIN
  DECLARE curr_date DATE;
  SET curr_date = start_date;
  WHILE curr_date <= end_date DO
    INSERT IGNORE INTO days(day)  VALUES(curr_date);
    SET curr_date = DATE_ADD(curr_date, INTERVAL 1 DAY);
  END WHILE;
END

You can then call this procedure to create days like this:

CALL make_days('2011-01-01','2012-12-31');

Now you can create values for every minute in a given time interval using a query similar to the following:

SELECT YEAR(day) AS year, MONTH(day) AS month, DAYOFMONTH(day) AS day, hour, minute
FROM days, hours, minutes
WHERE CAST(CONCAT(day,' ',hour,':',minute) AS DATETIME) BETWEEN '2011-08-31 22:00' AND '2011-09-01 10:00'
ORDER BY year, month, day, hour, minute

How to group by mysql to fill empty row

Aggregate by name and take the max of each other column:

SELECT
    Name,
    MAX(key1) AS key1,
    MAX(key2) AS key2,
    MAX(key3) AS key3
FROM yourTable
GROUP BY
    Name;

This works because the MAX function, when used to aggregate, ignores NULL values. So, if a given column has a number of records only one of which is not NULL, that value would be retained after aggregating.

How to fill empty value when no record in that day using MySQL group by

You can use a trick to generate virtual table having all the dates you need with another table (replace aux with any table in your DB with 31 recored at least):

SELECT CONVERT(@d := DATE_ADD(@d, INTERVAL 1 DAY), DATE) AS `d`
FROM
    `aux`,
    (SELECT @d := DATE_SUB(CONVERT(DATE_FORMAT(NOW(), '%Y-%m-01'), DATETIME), INTERVAL 1 DAY)) `x`
WHERE
    @d < DATE_SUB(NOW(), INTERVAL 1 DAY)
LIMIT
    31

And then join you table on it:

SELECT
    `aux`.`d` as `Date`,
    SUM(IFNULL(`Clicks`, 0))AS `Clicks`,
    DAY(LAST_DAY(NOW())) AS `Monthdays`
FROM (
    SELECT CONVERT(@d := DATE_ADD(@d, INTERVAL 1 DAY), DATE) AS `d`
    FROM
        `aux`,
        (SELECT @d := DATE_SUB(CONVERT(DATE_FORMAT(NOW(), '%Y-%m-01'), DATETIME), INTERVAL 1 DAY)) `x`
    WHERE
        @d < DATE_SUB(NOW(), INTERVAL 1 DAY)
    LIMIT
        31
) aux
LEFT JOIN
    myTbl
    ON `Date` = `aux`.`d`
GROUP BY `aux`.`d`

MySQL: filling empty fields with zeroes when using GROUP BY

I've finaly found the answer.
Maybe I'm insane, but this works.

SELECT HOUR, max(HOUR_STAT) as HOUR_STAT FROM (
    (
        SELECT HOUR(TIMESTAMP_X) as HOUR, count(*) as HOUR_STAT
        FROM cms_webstat
        WHERE date(TIMESTAMP_X) = date(now())
    )
    UNION (SELECT 0 as HOUR, 0)
    UNION (SELECT 1 as HOUR, 0)
    UNION (SELECT 2 as HOUR, 0)
    UNION (SELECT 3 as HOUR, 0)
    UNION (SELECT 4 as HOUR, 0)
    UNION (SELECT 5 as HOUR, 0)
    UNION (SELECT 6 as HOUR, 0)
    UNION (SELECT 7 as HOUR, 0)
    UNION (SELECT 8 as HOUR, 0)
    UNION (SELECT 9 as HOUR, 0)
    UNION (SELECT 10 as HOUR, 0)
    UNION (SELECT 11 as HOUR, 0)
    UNION (SELECT 12 as HOUR, 0)
    UNION (SELECT 13 as HOUR, 0)
    UNION (SELECT 14 as HOUR, 0)
    UNION (SELECT 15 as HOUR, 0)
    UNION (SELECT 16 as HOUR, 0)
    UNION (SELECT 17 as HOUR, 0)
    UNION (SELECT 18 as HOUR, 0)
    UNION (SELECT 19 as HOUR, 0)
    UNION (SELECT 20 as HOUR, 0)
    UNION (SELECT 21 as HOUR, 0)
    UNION (SELECT 22 as HOUR, 0)
    UNION (SELECT 23 as HOUR, 0)
)
AS `combined_table`
GROUP BY HOUR
ORDER BY HOUR DESC

One MySQL query as desired.

How to fill blank while using GROUP BY WITH ROLLUP in MySQL

Here is one method:

SELECT COALESCE(id,'TOTAL'),
       (case when id is null then null else description end) as description,
       sum(qty) AS SUM
FROM item
group by id WITH ROLLUP;

MySQL: fill empty strings with any non-empty value in a group

With this query (for MySql 5.7+):

SELECT test_name, ANY_VALUE(unit) unit
FROM tests
WHERE unit IS NOT NULL
GROUP BY test_name

you get for each test_name a non null unit (provided there is at least 1 non null unit for this test_name).

Join it to the table in the UPDATE statement:

UPDATE tests t1
INNER JOIN (
  SELECT test_name, ANY_VALUE(unit) unit
  FROM tests
  WHERE unit IS NOT NULL
  GROUP BY test_name
) t2 ON t2.test_name = t1.test_name
SET t1.unit = t2.unit
WHERE t1.unit IS NULL;

See the demo.

Instead of ANY_VALUE() you can use MIN() or MAX().

MySQL Query to fill NULL values with GROUP BY HOUR

This must work:

SELECT
  rh.hour,
  COUNT(ce.id) incoming,
  COUNT(IF(ce.datetime_init IS NULL,1,NULL)) dropped,
  COUNT(IF(ce.datetime_init IS NOT NULL,1,NULL)) answered
FROM
  call_center.report_hours rh
LEFT JOIN
  call_center.call_entry ce ON
    HOUR(ce.datetime_entry_queue)=rh.hour AND
    ce.datetime_entry_queue>='2014-09-05 15:00:00' AND
    ce.id_campaign='1'
GROUP BY rh.hour

Whats wrong with your query:

1. COUNT(IF(ce.datetime_init IS NULL,1,0)) - counts all rows, to work correctly I change 0 to NULL. Also can be changed to: SUM(IF(ce.datetime_init IS NULL,1,0))

2. When use LEFT JOIN table_name all conditional expressions related to specified table must by under LEFT JOIN ... ON clause. If they are under WHERE then effectively filters all rows with NULL values (when no match in call_center.call_entry table).

---

Related Topics