Rick Rick - 2 months ago 14
MySQL Question

MySQL query to create summary row for query with HAVING and LIMIT clauses

I'm having issues figuring out how to write a MySQL query that can create a summary row for queries that use HAVING and LIMIT clauses. Here is some sample data and such to recreate my issue..

Sample Table

CREATE TABLE `user` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`logins` int(10) unsigned DEFAULT NULL,
`date` date DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;


Sample Data

INSERT INTO `user` (`id`, `logins`, `date`)
VALUES
(1,1,'2016-09-25'),
(2,1,'2016-09-25'),
(3,2,'2016-09-26'),
(4,2,'2016-09-26'),
(5,3,'2016-09-27'),
(6,3,'2016-09-27'),
(7,4,'2016-09-28'),
(8,4,'2016-09-28'),
(9,5,'2016-09-29'),
(10,5,'2016-09-29');


And here is a my base query showing full results.

mysql> select `date`, sum(`logins`) `logins` from `user` group by `date`;
+------------+--------+
| date | logins |
+------------+--------+
| 2016-09-25 | 2 |
| 2016-09-26 | 4 |
| 2016-09-27 | 6 |
| 2016-09-28 | 8 |
| 2016-09-29 | 10 |
+------------+--------+


No problem to get a summary row off of that though right..

mysql> select sum(`logins`) `logins` from `user`;
+--------+
| logins |
+--------+
| 30 |
+--------+


But what if I wanted to build a summary row out of something like the query below? In this example I've added the HAVING and LIMIT clauses which is meant to simulate a very large data set. The HAVING clause in this query represents what a user could alter as a max amount within filters provided the them in the interface. Limit 0,2 represents page 1 of the results (out of 3 in this case: 0,2; 2,4; 4,6). These queries are part of a paging system used to display results by X,X amount at a time. Thus the reason for the LIMIT clause in the query..

mysql> select `date`, sum(`logins`) `logins` from `user` group by `date` having `logins` <= 8 limit 0, 2;
+------------+--------+
| date | logins |
+------------+--------+
| 2016-09-25 | 2 |
| 2016-09-26 | 4 |
+------------+--------+


The summary query that I'm needing for the above result set should tally up 6 logins in this case. How would I go about building a query to do something like this?

Now I cannot simply loop query results (client or server side) to tally up totals because the html table (server side generated) only displays the first page of results and that would only tally up counts displayed on that particular page. I am needing to display a summary row with counts of the full data set across all pages of table pagination, if that makes any sense..

UPDATE

Here is a sort of sample of the desired result..

+------------+--------+
| date | logins |
+------------+--------+
| 2016-09-25 | 2 |
| 2016-09-26 | 4 |
+------------+--------+
| TOTAL | 20 | <-- JUST NEED A QUERY TO PRODUCE THIS NUMBER SOME HOW..
+------------+--------+


I'm sorry guys.. I meant to say 20 should be the number, not 6.. Becuase the full result set with the HAVING filter would be so:

mysql> select `date`, sum(`logins`) `logins` from `user` group by `date` having `logins` <= 8;
+------------+--------+
| date | logins |
+------------+--------+
| 2016-09-25 | 2 |
| 2016-09-26 | 4 |
| 2016-09-27 | 6 |
| 2016-09-28 | 8 |
+------------+--------+


SORRY!

It's almost like I need a query like so (no group by):

select `date`, sum(`logins`) `logins` from `user` having `logins` <= 8


But obviously that does not work..

FINAL UPDATE

Well here's my 20 based off of @Shadow's answer below which is a very simple example but eventually led me to the answer I needed:

mysql> select sum(`logins`) `logins` from (select `date`, sum(`logins`) `logins` from `user` group by `date` having `logins` <= 8) t;
+--------+
| logins |
+--------+
| 20 |
+--------+

Answer

You need to do the summing up after the having and limit clauses are applied. Either do this in the presentation layer when you display the numbers or you have to have the 2nd query as a subquery in sql and do the sum in the outer query.

select sum(logins) as logins from
    (select `date`, sum(`logins`) `logins` from `user` group by `date` having `logins` <= 8 limit 0, 2) t