MYSQL multiple conditional statements for count

Question

I'm very new to MYSQL, have looked at many answers on this site but can't get the following to work...

Table is "member" 3 fields are "id" (Integer); and 2 date fields "dob" and "expiry" I need to count the number of records where all are current members, ie expiry<curdate() then I need to know the count of records with the following conditions: year(curdate())-year(dob) <25 as young year(curdate())-year(dob) >25 and <=50 as Medium year(curdate())-year(dob) >50 as Older

So I expect to get a single row with many columns and the count of each of these conditions.

Effectively I'm filtering current members for their age grouping.

I've tried a subquery but failed to get that to work.

Thanks

See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Apr 11 '17 at 05:26
Possible duplicate of [Count with if condition in mysql query](http://stackoverflow.com/questions/9798937/count-with-if-condition-in-mysql-query) — Shadow, Apr 11 '17 at 05:29
You may not need a group by, but the conditional counting is described in the answers. — Shadow, Apr 11 '17 at 05:31

score 0 · Answer 1 · answered Apr 11 '17 at 11:41

If you really want the end result as you have mentioned, you could use views. It takes a long way to achieve the result. However, here is the way. I created the following table member and inserted data as follows.

CREATE TABLE member (
    id int(11) AUTO_INCREMENT PRIMARY KEY,
    dob date DEFAULT NULL,
    expiry date DEFAULT NULL
);

INSERT INTO member (id, dob, expiry) VALUES
    (1, '1980-01-01', '2020-05-05'),
    (2, '1982-05-05', '2020-01-01'),
    (3, '1983-05-05', '2020-01-01'),
    (4, '1981-05-05', '2020-01-01'),
    (5, '1994-05-05', '2020-01-01'),
    (6, '1992-05-05', '2020-01-01'),
    (7, '1960-05-05', '2020-01-01'),
    (8, '1958-05-05', '2020-01-01'),
    (9, '1958-07-07', '2020-05-05');

Following is the member table with data.

id |    dob      |    expiry
--------------------------------
1  | 1980-01-01  |  2020-05-05
2  | 1982-05-05  |  2020-01-01
3  | 1983-05-05  |  2020-01-01
4  | 1981-05-05  |  2020-01-01
5  | 1994-05-05  |  2020-01-01
6  | 1992-05-05  |  2020-01-01
7  | 1960-05-05  |  2020-01-01
8  | 1958-05-05  |  2020-01-01
9  | 1958-07-07  |  2020-05-05

Then I created a separate view for all the current employees named as current_members as follows.

CREATE VIEW current_members AS (SELECT * FROM member WHERE TIMESTAMPDIFF(YEAR, CAST(CURRENT_TIMESTAMP AS DATE), member.expiry) >= 0);

Then querying from that view, I created 3 separate views containing counts for each age ranges of young, middle and old as follows.

CREATE VIEW young AS (SELECT COUNT(*) as Young FROM (SELECT TIMESTAMPDIFF(YEAR, current_members.dob, CAST(CURRENT_TIMESTAMP AS DATE)) AS age FROM current_members HAVING age <= 25) yng);
CREATE VIEW middle AS (SELECT COUNT(*) as Middle FROM (SELECT TIMESTAMPDIFF(YEAR, current_members.dob, CAST(CURRENT_TIMESTAMP AS DATE)) AS age FROM current_members HAVING age BETWEEN 25 AND 50) mid);
CREATE VIEW old AS (SELECT COUNT(*) as Old FROM (SELECT TIMESTAMPDIFF(YEAR, current_members.dob, CAST(CURRENT_TIMESTAMP AS DATE)) AS age FROM current_members HAVING age >= 50) old);

Finally, the three views were cross joined in order to get the counts of each age range into a single row of one final table as follows.

SELECT * FROM young, middle, old;

This will give you the following result.

Young | Middle | Old
----------------------
   2  |    4   |  3

SUGGESTION : FOR THE ABOVE TEDIOUS TIME DIFFERENCE CALCULATIONS, YOU COULD WRITE YOUR OWN STORED PROCEDURE TO SIMPLIFY THE CODE

MYSQL multiple conditional statements for count

1 Answers1