Sum The Sub Query Result

Question

Table Example:

| date_time           | quantity
-------------------------
| 2017-08-12 09:11:51 | 1
| 2017-08-12 10:12:51 | 1
| 2017-08-12 12:45:51 | 1
| 2017-08-13 11:12:51 | 1
| 2017-08-13 11:12:51 | 1
| 2017-08-13 12:45:51 | 1
| 2017-08-13 13:57:51 | 1
| 2017-08-14 14:23:51 | 1
| 2017-08-14 16:34:51 | 1
| 2017-08-15 16:21:51 | 1
| 2017-08-16 14:31:51 | 1

I can get the result with this query:

SELECT DATE(date_time) as date_time
    ,SUM(quantity) as quantity
FROM report
WHERE date_time BETWEEN '' AND  ''
GROUP BY DATE(date_time)
HAVING SUM(quantity) < 3 

Result:

| date_time  | quantity
 -------------
| 2017-08-14 | 2
| 2017-08-15 | 1
| 2017-08-16 | 1

I want to SUM quantity afterwards. For example:

 quantity
---------
| 4

To achieve this I know I need to use the result as sub query to sum afterwards. I tried this but it's wrong:

SELECT SUM(topla) FROM (
  ( SELECT DATE(date_time) as date_time
        ,SUM(quantity) as quantity
    FROM report
    WHERE date_time BETWEEN '' AND  ''
    GROUP BY DATE(date_time)
    HAVING SUM(quantity) < 3 
  ) AS topla
)

How can I SUM the quantity?

Answer 1

First you have to sum the quantity by column name. (In the example I named it sub_quantity)

Second you do not need to select the date.

So your query could be

SELECT SUM(sub_quantity) AS quantity
FROM
  ( SELECT SUM(quantity) AS sub_quantity
   FROM report
   WHERE date_time BETWEEN '' AND ''
   GROUP BY DATE(date_time)
   HAVING SUM(quantity) < 3) a

Answer 2

Just replace the topla with quantity and remove the 'as topla' from the query before topla.

SELECT SUM(quantity) total FROM (
  ( SELECT DATE(date_time) as date_time
        ,SUM(quantity) as quantity
    FROM report
    WHERE date_time BETWEEN '' AND  ''
    GROUP BY DATE(date_time)
    HAVING total < 3 
  )
)

To use SUM function you have to give column name not the table name.