Count of Items Based on Todays Date in MySQL

Question

I have 2 tables one has the user_info and another has user_activies.

I have a query that fetches several rows from user_info table. What I want to add to this query is;

I want to fetch todays user activities and count them from user_activities table.

user_info
| id | name | views | lastlogin | regdate | group_id |

user_activities
| id | userid | activity | date |

Current query

select id, name, views, lastlogin 
from user_info 
where group_id = 2 
ORDER BY user_info.id ASC

How could I concatenate count of the total number of activities has been done today?

Thanks in advance!

Can you provide minimal sample data and then expected output from this data ? — Oto Shavadze, Apr 28 '17 at 08:05

Martin B. · Answer 1 · 2017-04-28T08:35:10.527

2

~~You want this:~~

SELECT i.id, i.name, i.views, i.lastlogin, Count(a.id)
  FROM user_info i, user_activities a
  WHERE i.group_id = 2 AND i.id = a.userid
    AND a.date = CURDATE()
  ORDER BY user_info.id ASC;

~~This gives you a record of every user with a count of todays activities. (You may need to change the `a.date = CURDATE()` to fit your timestamp needs)~~

This will not give you a list of ervery user. Instead you will have to select a single user. If you select multiple users you will get a random name with the sum of all activities from all selected users.
Or short: This does not solve your problem. Take the JOIN-solution.

edited Apr 28 '17 at 08:35

answered Apr 28 '17 at 08:14

Martin B.

1,567
14
26

Hi! This returns null for some reason, by the way my date type is `date text utf8_general_ci` but stored as this format: `2017-04-28 11:33:05` – Lunatic Fnatic Apr 28 '17 at 08:33
I just found out that this solution is flawed. It does not work for multiple users. – Martin B. Apr 28 '17 at 08:36
1

Also you will have to strip the time from this timestamp using `DATE(date)` to get the yyy-mm-dd part of the date and compare it with the current day. – Martin B. Apr 28 '17 at 08:37
@MartinB. goo job too ^^ – Charif DZ Apr 28 '17 at 08:48

Charif DZ · Accepted Answer · 2017-04-28T08:45:30.600

2

i'm assuming that date is of type date:

select 
   u.id,
   u.name,
   u.views,
   u.lastlogin,
    sum(
  -- if datetime IF (date(date) = curdate() , 1, 0)
  IF (date = curdate() , 1, 0)
     ) as 'today_activities'

from user_info u 
      -- using left join you will get the list of all user even if they
      -- don't have any activities today with sum activities= 0
      LEFT JOIN user_activities a on u.id = a.userid
where 
   group_id = 2
group by u.id
ORDER BY u.id ASC

edited Apr 28 '17 at 08:45

answered Apr 28 '17 at 08:17

Charif DZ

14,415
3
21
40

i edit my answer to handle datetime if the type is datetime – Charif DZ Apr 28 '17 at 08:22
Is there a performance difference between using a `JOIN` instead of using 2 tables in the `FROM` clause? – Martin B. Apr 28 '17 at 08:25
http://stackoverflow.com/questions/121631/inner-join-vs-where take a look at this answer – Charif DZ Apr 28 '17 at 08:30
@CherifOdoo Hi Cherif, my date type is text but stored as date format such as `YYYY-MM-DD HH:MM:SS`. How could I count the date when my date stored in text type? – Lunatic Fnatic Apr 28 '17 at 08:36
1

ok you need to convert it there is a function for that i think wait a minute – Charif DZ Apr 28 '17 at 08:37
Thanks to @MartinB. I have managed to make this work. I used your code and changed `date = curdate()` to `date(date) = curdate()` and also updated `u.id = a.user_activities` to `u.id = a.userid` – Lunatic Fnatic Apr 28 '17 at 08:41
`IF (date(STR_TO_DATE(date,'%Y-%m-%d')) = curdate() , 1, 0)` – Charif DZ Apr 28 '17 at 08:42
@CherifOdoo This has also worked, thank you both I really apprecaite! :-) – Lunatic Fnatic Apr 28 '17 at 08:45
sorry for my mistake on joining – Charif DZ Apr 28 '17 at 08:46

score 0 · Answer 3 · answered Apr 28 '17 at 08:19

0

you can try this:

SELECT i.id, i.name, i.views, i.lastlogin, Count(a.activities)
  FROM user_info i inner join user_activities a
  WHERE i.group_id = 2 AND i.id = a.userid
    AND a.date = now()
  ORDER BY user_info.id

answered Apr 28 '17 at 08:19

Fahmi

37,315
5
22
31

Count of Items Based on Todays Date in MySQL

3 Answers3