Advance sql query

Question

How to build sql query in this situation:

I have to tables (only showing table columns)

Table one

- Users -> ID, First name, Last name....

Table two

– events -> ID, UserID, statID

I want to get sql results like this:

ID,
First name,
Last name,
count(from table event where stat == 11 and (between date1 and date2)), 
count(from table event where stat =? 8 and (between date1 and date2))

Please help me

Answer 1

This is a sql server based solution:

SELECT u.ID, 
       u.[First Name],
       u.[Last Name],
       SUM(CASE WHEN e.statID = 11 AND e.date >= date1 AND e.date<= date2 
                THEN 1
                ELSE 0
           END) AS CountStat11, 
       SUM (CASE WHEN e.StatID= 8 AND e.date >= date1 AND e.date<= date2 
                 THEN 1
                 ELSE 0
            END) AS CountStat8
FROM users AS u LEFT JOIN events AS e ON u.ID = e.userid
GROUP BY u.ID, u.[First Name], u.[Last Name]

Here I used a LEFT JOIN to give you results for all users even if they didn't have any event. If you interested only in those with event id then you need to use INNER JOIN.