CODEWITHSUNDEEP
mysqlselectcount
Muhammad Haryadi Futra Iskanda
Muhammad Haryadi Futra Iskanda

Reputation: 105

How to select and select count in one query in mysql?

I have this table:

Activity     Date    
reading   12-10-2014
watching  12-10-2014
reading   13-10-2014
reading   12-10-2014
watching  13-10-2014

What I want to do is selecting the activity and count the activity number based on date, I want the output will be like this (with condition: where date ='12-10-2014'):

Activity   count    date
reading      2    12-10-2014
watching     1    12-10-2014

How can I do that?, help me please. Thanks.

Upvotes: 3

Views: 2258

Answers (2)

pjd
pjd

Reputation: 1173

This should work:

select activity, count(activity) as count, date from my_table
where date = '12-10-2014' group by activity;

(not sure about your column labels--you may have to adjust for capitalization)

Upvotes: 0

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44373

SELECT Activity,COUNT(1) `count`,date
FROM mytable WHERE date='12-10-2014'
GROUP BY date,Activity;

or 

SELECT Activity,COUNT(1) `count`,date
FROM mytable WHERE date='2014-10-12'
GROUP BY date,Activity;

Make sure you table has an index on date and Activity

ALTER TABLE mytable ADD INDEX date_Activity_ndx (date,Activity);

Upvotes: 1

Related Questions