challenging query

[maskme]

Dear All

I've a table which stores values of people attending sports class in different locations in 7 days

For e.g.

Record ID | Location | Day

1 | LA | Day 1
1 | LA | Day 2
1 | LA | Day 3
2 | BA | Day 1
2 | BA | Day 4
2 | BA | Day 7

The above is just a sample I've records of 30,000 where people attend every day till Day 7 and some only attend Day 1 or Day 3 etc...

Now I want the following results

The first question would be how many people attended every day? - I got this One (means all Day 1, Day 2, Day 3, Day 4, Day 5, Day 6, Day 7)

second how many missed a day or more?
(not able to get this one)

third how many people attended for less than 3 days (can be any either Day 1, Day 2, Day 3, Day 4, Day 5, Day 6, Day 7)?
(not able to get this one)

fourth how many people attended only on Day 7?
(not able to get this one)

Any help would be really great!!!!!!!!

Thanks a lot.

[johanafm]

Assuming:
1. each value of record ID means a unique individual
2. all sports classes in all locations always have 7 days.

SELECT count(*)
FROM (
	SELECT id, location, COUNT(day) AS dayCount
	FROM tbl
	GROUP BY id, location
	-- Missed one or more days
	HAVING dayCount < 7
	-- Attended less than 3 days: HAVING dayCount < 3
	-- Attended all 7 days: HAVING dayCount = 7
) AS t

But, from this table alone it is not possible to get information about those not attending at all. This means that if everyone never attended, the above query would show 0 people missing one or more days, 0 people attending less than 3 days and 0 people attending all days.

[maskme]

thanks!!! It's working 🙂