How to merge multiple queries into a single query in MySQL

Question

Suppose I have a table called "productClick" like this:

----id------click-------------ctime----------
|    1  |    5     |   2015-12-26 00:01:12  |
---------------------------------------------
|    2  |    2     |   2015-12-27 00:01:12  |
---------------------------------------------
|    3  |    7     |   2015-12-28 00:01:12  |
---------------------------------------------
|    4  |    1     |   2015-12-28 00:01:12  |
---------------------------------------------

I want to get total number of product, today's total number of product added and sum of total click.

I have three separate queries like this:

SELECT COUNT(*) as totalProduct FROM productClick
SELECT COUNT(*) as todayTotalProductAdded FROM productClick WHERE DATE(`ctime`) = CURDATE()
SELECT sum(click) as totalClick FROM productClick

It works fine. But I want to combine this three separate queries into one query. How to achieve this?

UPDATE

Question updated. Sorry for my fault. Thanks @Gordon Linoff

Is todayClick really COUNT(*)? Shouldn't it be SUM(click)? — Gordon Linoff
– Gordon Linoff, Commented Dec 28, 2015 at 11:59

Lukasz Szozda · Accepted Answer · 2015-12-28 12:08:42Z

3

You could use conditional aggregation:

SELECT COUNT(*) as totalProduct,
       SUM(click) as totalClick,
       SUM(DATE(ctime) = CURDATE()) as todayClick
FROM productClick

The todayClick is shorthand for:

SUM(CASE WHEN DATE(ctime) = CURDATE() THEN 1 ELSE 0 END) as todayClick

or UNION ALL if you want multiple rows:

SELECT 'totalProduct' AS counter_name, COUNT(*) AS counter_value FROM productClick
UNION ALL
SELECT 'todayClick', COUNT(*) FROM productClick WHERE DATE(ctime) = CURDATE()
UNION ALL 
SELECT 'totalClick', SUM(click) FROM productClick

EDIT:

today's total number of product added and sum of total click.

SELECT COUNT(*) as totalProduct,
       SUM(click) as totalClick,
       SUM(CASE WHEN DATE(ctime) = CURDATE() THEN click ELSE 0 END) as todayClick
FROM productClick

edited Dec 28, 2015 at 12:08

answered Dec 28, 2015 at 11:58

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Ashis Biswas Over a year ago

Works fine. Thanks. But can you explain me this line SUM(DATE(ctime) = CURDATE()) ?

Lukasz Szozda Over a year ago

@AshisBiswas When DATE(ctime) = CURDATE() you get true or false. When summing it false is represented by 0 and true by 1. Demo. At the end it is just summing 1 and 0.

Ashis Biswas Over a year ago

Thanks you so much. Got it. :)

Carlos Spohr Over a year ago

I think using unions in your sql command can be more readable. Using boolean conditions inside sum, count, for instance, it only will sum or count when it's true. Consider sum 'null' for certain cases when 0 (zero) is a 'value'.

Lukasz Szozda Over a year ago

@CarlosA.Junior That's exactly the point of conditional aggregation. Null will be handled properly as in each query itselt. First query is COUNT(*) the same as OP, SUM(click) the same as OP, the same with todayClick. See I wrote boolean condtions as a shorthand. If you need different value or column use full CASE WHEN. Using UNION ALL over multiple columns depends on requirements. For counters I like one row with multiple values, but as always it depends.

Collectives™ on Stack Overflow

How to merge multiple queries into a single query in MySQL

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related