MySQL Query for count of multiple values

Question

I'm trying to retrieve the count of multiple values but can get only partial count.

My code

SELECT COUNT(*) as count, `dateadded` FROM s2 WHERE 'LEVEL` IN (1,2,3) and 
client = 'myuser' GROUP BY `LEVEL`,`dateadded` ORDER BY `dateadded` DESC 
LIMIT 1");

My table

client   dateadded     level
myuser   2019-01-21      3
myuser   2019-01-21      2
myuser   2019-01-21      5
myuser   2019-02-16      3
myuser   2019-02-16      2
myuser   2019-02-16      8
myuser   2019-02-16      2

My return value should be: 3 -> latest date (2019-02-19) and count of 1,2 and 3.

I want to count how many 1, 2 and 3 from the latest date only.

Thank you very much!!

Nathalie

Can you please show what your expected output should look like? — Mr ML
– Mr ML, Commented Feb 21, 2019 at 7:07
You can't get the "level" of one entry and also the count of each level in one SQL query. — Marvin Klar
– Marvin Klar, Commented Feb 21, 2019 at 7:18

Krishnakumar · Accepted Answer · 2019-02-21 07:09:03Z

1

While your requirement is not clear, I assume you wanted the count of LEVEL, dateadded for the latest date alone.

This should help you:

SELECT COUNT(*) as count, `dateadded` , `LEVEL` FROM s2 WHERE `LEVEL` IN 
(1,2,3) and 
client = 'myuser' GROUP BY `dateadded`,`LEVEL` ORDER BY `dateadded` DESC 
LIMIT 1;

If you don't want to count by each level, then use this:

SELECT COUNT(*) as count, `dateadded` FROM s2 WHERE `LEVEL` IN 
(1,2,3) and 
client = 'myuser' GROUP BY `dateadded` ORDER BY `dateadded` DESC 
LIMIT 1;

answered Feb 21, 2019 at 7:09

Krishnakumar

7451 gold badge6 silver badges11 bronze badges

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

1 Comment

enkiki Over a year ago

Last one was the key one! Thank you so much! :)

Gaurav Neema · Accepted Answer · 2019-02-21 07:10:26Z

0

Try this:

SELECT COUNT(*) as count, `dateadded` FROM s2 WHERE 'LEVEL' IN (1,2,3) and 
client = 'myuser' and `dateadded` IN (select max(dateadded) from s2) GROUP BY `LEVEL`,`dateadded` ORDER BY `dateadded` DESC 
LIMIT 1");

answered Feb 21, 2019 at 7:10

Gaurav Neema

1561 gold badge1 silver badge12 bronze badges

Collectives™ on Stack Overflow

MySQL Query for count of multiple values

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related