Group by with Subquery

Transact-SQL
1

Here is my DB schema and my query:
http://sqlfiddle.com/#!9/ebfabb/2/0

This query gives me the following result:
Sex totalMail NoClick Click
Female 2 1 1
male 2 1 1

But I would need the totalMail for females to be 3 instead of 2, because they still got the email, even though they haven't clicked in the requested time span.
Sex totalMail NoClick Click
Female 3 1 1
male 2 1 1

So I need to group my result by sex, but I only want to count the "clicks" that are in the corresponding time span as in my query. however the count(mailsent_number) must always be done on ALL records, regardless of whether that user clicked, didn't click or clicked outside the requested time span.

I have no idea how I can achieve that in one SQL query.

2

Solved it this way:

select sex,
count(mailsent_number) TotalMail,
count(mailsent_number) - sum(
  CASE WHEN click_time < '2017-08-14 10:30' or click_time is null THEN click END
  ) NoClick,
count(click) Click
from Table1 group by sex;

Note that your check for time may fail if time of day is before 10 am, since you're doing character compare. you can't count on a leading zero. Why not store the datetime as a datetime type?

1 Like
3

Brilliant. That seems to work. Thanks a lot!