Tripp Williamson Tripp Williamson - 5 months ago 19
SQL Question

SQL Multiple Records SUM

Here is my 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 exactly what i want.

|fac|sumA|sumB|sumC|sumD|
+---+----+----+----+----+
| 1|x | | | |


But im trying to do loop the query to output 5
fac


End result should look like this..

|fac|sumA|sumB|sumC|sumD|
+---+----+----+----+----+
| 1|x | | | |
+---+----+----+----+----+
| 2|x | | | |
+---+----+----+----+----+
| 3|x | | | |
+---+----+----+----+----+
| 4|x | | | |
+---+----+----+----+----+
| 5|x | | | |

Answer
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`
Comments