CODEWITHSUNDEEP
mysqlsql
Lunatic Fnatic
Lunatic Fnatic

Reputation: 681

Count of Items Based on Todays Date in MySQL

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!

Upvotes: 0

Views: 47

Answers (3)

Charif DZ
Charif DZ

Reputation: 14721

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

Upvotes: 2

Martin B.
Martin B.

Reputation: 1663

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.

Upvotes: 2

Fahmi
Fahmi

Reputation: 37473

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

Upvotes: 0

Related Questions