I have created a table as shown below:
CREATE TABLE myRecord(stu_name VARCHAR(50),gender VARCHAR(50),level VARCHAR(50));INSERT INTO myRecord VALUES('John','Male','Pre-Primary');
INSERT INTO myRecord VALUES('Jane','Female','Year 1');
INSERT INTO myRecord VALUES('Sue','Female','Year 2');
The expected result is supposed to be the following:
Male | Female | Level
----------------------
1 | 0 | Pre-Primary
But when I run the SQL below:
SELECT SUM(t.Male) AS Male,
SUM(t.Female) AS Female,
t.level
FROM
((SELECT SUM(gender='Male') as Male,
SUM(gender='Female') as Female,
CASE WHEN level='Pre-Primary'
THEN 'Pre-Primary'
END AS level
FROM myRecord
) t)
;
The output is as the following:
Male | Female | Level
----------------------
1 | 2 | Pre-Primary
It also calculated Year 1 and Year 2. I only want it to calculate each gender of the level where level = 'Pre-Primary'. I've tried using GROUP BY level but it gave me the same result. I've also tried where but it gave me an error.
Is there any possible way to do that? I've been working on this for several days but I couldn't solve it and it's kinda disappointing. I hope someone can help on this.
Perhaps you want a where clause:
SELECT SUM(gender = 'Male') AS Male, SUM(gender = 'Female') AS Female, level
FROM myRecord
WHERE level = 'Pre-Primary';
I don't see why a subquery would be necessary.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments