Tripp Williamson - 1 year ago 57

MySQL Question

I have a query...

`SELECT `fac` SUM(`a`),SUM(`b`),SUM(`c`),SUM(`d`)`

FROM `x` WHERE `dateTime` BETWEEN '2016-06-13 'AND '2016-06-14' AND `fac` = 1

...that outputs this:

`|fac|sumA|sumB|sumC|sumD|`

+---+----+----+----+----+

| 1|x | | | |

But I'm trying to loop the query to output

`fac`

`|fac|sumA|sumB|sumC|sumD|`

+---+----+----+----+----+

| 1|x | | | |

+---+----+----+----+----+

| 2|x | | | |

+---+----+----+----+----+

| 3|x | | | |

+---+----+----+----+----+

| 4|x | | | |

+---+----+----+----+----+

| 5|x | | | |

Answer Source

```
SELECT `fac`
, SUM(`a`)
, SUM(`b`)
, SUM(`c`)
, SUM(`d`)
FROM `x`
WHERE `dateTime` BETWEEN '2016-06-13' AND '2016-06-14'
AND `fac` IN (1,2,3,4,5)
GROUP BY `fac`
ORDER BY `fac`
```

If there are no rows for a given `fac`

that satisfy the condition on `dateTime`

, then there won't be a row for that `fac`

. That is, you aren't guaranteed that this query will return five rows; just like your original query isn't guaranteed to return a row.

For the BETWEEN range on `dateTime`

, that's going to include rows for midnight of `2016-06-14`

.

Those same rows will be included if you do `BETWEEN '2016-06-14' AND ...`

With datetime ranges, to get the rows from one day, we typically do a >= and a < range.

```
WHERE `datetime` >= '2016-06-13'
AND `datetime` < '2016-06-14'
```

If you want to always return five rows, with zeros when there aren't any rows for that `fac`

, you need a guaranteed row source for the five `fac`

values. Something like this:

```
SELECT i.`fac`
, IFNULL(SUM(t.`a`),0)
, IFNULL(SUM(t.`b`),0)
, IFNULL(SUM(t.`c`),0)
, IFNULL(SUM(t.`d`),0)
FROM ( SELECT 1 AS `fac`
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 4
UNION ALL SELECT 5
) i
LEFT
JOIN `x` t
ON t.`fac` = i.`fac`
AND t.`dateTime` >= '2016-06-13'
AND t.`dateTime` < '2016-06-14'
AND t.`fac` IN (1,2,3,4,5)
GROUP BY i.`fac`
ORDER BY i.`fac`
```