How to count two different columns in sql

I have a database which looks like this.

school
  exam
    firstname: varchar(40)
    name: varchar(40)
    lesson: varchar(30)
    item: smallint(6)
  lessons
    lesson: varchar(30)
    grade: smallint(6)
    abc: char(1)
    edate: date
  students
    firstname: varchar(40)
    name: varchar(40)
    grade: smallint(6)
    abc: char(1)

​x
 
school  exam    firstname: varchar(40)    name: varchar(40)    lesson: varchar(30)    item: smallint(6)  lessons    lesson: varchar(30)    grade: smallint(6)    abc: char(1)    edate: date  students    firstname: varchar(40)    name: varchar(40)    grade: smallint(6)    abc: char(1)​

My task is to get the columns of dates, number of exams on that date, number of students who write an exam on that date. Something like Date – Exam Count – Student Count. I have done this but in two different queries.

SELECT l.edate, COUNT(l.edate) AS Exams
FROM lessons l
WHERE (l.edate IS NOT NULL)
GROUP BY l.edate
ORDER BY l.edate DESC

 
SELECT l.edate, COUNT(l.edate) AS ExamsFROM lessons lWHERE (l.edate IS NOT NULL)GROUP BY l.edateORDER BY l.edate DESC​

Result: edate Exams | ==================| 2020-05-24 | 1 | 2020-05-23 | 1 | 2020-05-22 | 2 | 2020-05-20 | 1 | 2020-05-19 | 1 | 2020-05-18 | 1 | 2020-05-16 | 2 | 2020-05-15 | 2 | 2020-05-14 | 5 | ==================|

SELECT l.edate, COUNT(l.edate) AS Writing
FROM lessons l
JOIN students s
ON (l.abc = s.abc) AND (l.grade = s.grade)
WHERE (l.edate IS NOT NULL)
GROUP BY l.edate
ORDER BY l.edate DESC

 
SELECT l.edate, COUNT(l.edate) AS WritingFROM lessons lJOIN students sON (l.abc = s.abc) AND (l.grade = s.grade)WHERE (l.edate IS NOT NULL)GROUP BY l.edateORDER BY l.edate DESC​

Result: edate Writing | ====================| 2020-05-24 | 23 | 2020-05-23 | 27 | 2020-05-22 | 40 | 2020-05-20 | 30 | 2020-05-19 | 27 | 2020-05-18 | 25 | 2020-05-16 | 52 | 2020-05-15 | 34 | 2020-05-14 | 116 | ====================|

After I tried to combine these two queries into one and I got this, but it gives me the same count at every row, see the image below:

I know that’s because of adding new table, but how do I make it work?

SELECT l.edate, COUNT(l.edate) AS Exams, COUNT(s.firstname) AS Writing
FROM lessons l
JOIN students s
ON (l.abc = s.abc) AND (l.grade = s.grade)
WHERE (l.edate IS NOT NULL)
GROUP BY l.edate
ORDER BY l.edate DESC

 
SELECT l.edate, COUNT(l.edate) AS Exams, COUNT(s.firstname) AS WritingFROM lessons lJOIN students sON (l.abc = s.abc) AND (l.grade = s.grade)WHERE (l.edate IS NOT NULL)GROUP BY l.edateORDER BY l.edate DESC​

Answer

You’ve almost got it. Just combine your two queries:

SELECT E.edate, Exams, Writings FROM
(
  SELECT l.edate, COUNT(l.edate) AS Exams
  FROM lessons l
  WHERE (l.edate IS NOT NULL)
  GROUP BY l.edate) As E
JOIN
(
  SELECT l.edate, COUNT(l.edate) AS Writings
  FROM lessons l
  JOIN students s
  ON (l.abc = s.abc) AND (l.grade = s.grade)
  WHERE (l.edate IS NOT NULL)
  GROUP BY l.edate) As W ON E.edate=W.edate
ORDER BY E.edate

 
SELECT E.edate, Exams, Writings FROM(  SELECT l.edate, COUNT(l.edate) AS Exams  FROM lessons l  WHERE (l.edate IS NOT NULL)  GROUP BY l.edate) As EJOIN(  SELECT l.edate, COUNT(l.edate) AS Writings  FROM lessons l  JOIN students s  ON (l.abc = s.abc) AND (l.grade = s.grade)  WHERE (l.edate IS NOT NULL)  GROUP BY l.edate) As W ON E.edate=W.edateORDER BY E.edate​

NOTE: Not tested, no MySQL on current box

Advertisement

Answer