CODEWITHSUNDEEP
mysqlselectsubquery
Rixhers Ajazi
Rixhers Ajazi

Reputation: 1313

Issue with subquery

I need to run a report on the total amount of students per day. The issue I am having is that in my subquery I can not do a proper group by so that within the day I want to know how many came in for a specific reason.

My query is : 

SELECT 
     DATE_FORMAT(session.signintime, '%b %d %Y') Date, 
     COUNT(session.session_id) 'Total', 
     (SELECT COUNT(aidyear) FROM session WHERE aidyear = '12-13') '12-13',         
     (SELECT COUNT(aidyear) FROM session WHERE aidyear = '13-14') '13-14' 
FROM session 
WHERE status = '3' 
GROUP BY Date;

The resulting report is : 

+-------------+-------+-------+-------+
| Date        | Total | 12-13 | 13-14 |
+-------------+-------+-------+-------+
| Apr 15 2013 |    47 |    38 |    25 |
| Apr 16 2013 |     5 |    38 |    25 |
+-------------+-------+-------+-------+
2 rows in set (0.00 sec)

As you notice for April 16 2013 there is a total of 5 students only. Now notice that the 12-13 13-14 for both rows though are identical.

When I try to do a group by within the sub query I get this error :

ERROR 1241 (21000): Operand should contain 1 column(s)

meaning the sub query is returning more then one row to my understanding.

I tried with this query : 

SELECT 
      DATE_FORMAT(session.signintime, '%b %d %Y') Date, 
      COUNT(session.session_id) 'Total', 
      (SELECT DATE_FORMAT(session.signintime, '%b %d %Y') Date, 
      COUNT(aidyear) FROM session WHERE aidyear = '12-13' GROUP BY Date) '12-13',   
      (SELECT DATE_FORMAT(session.signintime, '%b %d %Y') Date, COUNT(aidyear) FROM session WHERE aidyear = '13-14' GROUP BY Date) '13-14' 
FROM session WHERE status = '3' 
GROUP BY Date;

Edit 1 As requested by Evan

The table I am querying from is the session table only. I am not joining (as you noticed)

The table has the following : 

Primary Key : session_id
Foreign Key : anum(which is a student ID)

Then we have: why, aidyear, signintime, studentcomments, status

Upvotes: 2

Views: 82

Answers (1)

Taryn
Taryn

Reputation: 247720

It seems like you should be able to use a CASE with the aggregate:

SELECT DATE_FORMAT(session.signintime, '%b %d %Y') Date, 
  COUNT(session.session_id) 'Total', 
  sum(case when aidyear = '12-13' then 1 else 0 end) '12-13',
  sum(case when aidyear = '13-14' then 1 else 0 end) '13-14'
FROM session 
WHERE status = '3' 
GROUP BY Date;

Upvotes: 3

Related Questions