Consolidating a COUNT query

Question

I have a page where I am running an initial SQL query to get a list of subjects, then I loop over this query and run two additional queries for each record returned from the original subjects query (I happen to be doing this in ColdFusion, but not sure that really matters). These two additional queries do COUNTs for that specific subject and then output the results (print the Subject name, then the two counts for that subject). I was trying to improve performance for this page, and wanted to know if I could somehow combine this into a single query.

Initial Query:

   SELECT subject_ID, subject_name
   FROM Subjects
   ORDER BY subject_name

Queries inside the loop of the initial query:

   SELECT COUNT(test_ID) as priority_count
   FROM Test_Queue
   WHERE priority_flag = 1 AND subject_ID = #SubjectQuery.subject_ID#

   SELECT COUNT(test_ID) as locked_count
   FROM Test_Queue
   WHERE locked = 1 AND subject_ID = #SubjectQuery.subject_ID#

Suggestions on how these might be optimized? DB is MS SQL 2008. Thanks.

Answer 1

SELECT 
    subject_ID, 
    subject_name,
    priority_count = (select count(test_id) from test_queue where priority_flag = 1),
    locked_count = (select count(test_id) from test_queue where locked = 1)
FROM Subjects
ORDER BY subject_name

or, if the counts are supposed to incorporate subject_id (just a guess), then

SELECT 
    s.subject_ID, 
    s.subject_name,
    priority_count = (select count(test_id) from test_queue t where priority_flag = 1 and t.subject_id = s.subject_id),
    locked_count = (select count(test_id) from test_queue t where locked = 1 and t.subject_id = s.subject_id)
FROM Subjects s
ORDER BY subject_name

Answer 2

This should do it, presuming that the join column from subjects to test_queue is correct, you haven't specified which column in test_queue references subjects

select
    subjects.subject_id
   ,subjects.subject_name
   ,sum(case when test_queue.priority_flag=1 THEN 1 ELSE 0 END) as priority_count
   ,sum(case when test_queue.locked=1 THEN 1 ELSE 0 END) as locked_count
  from
    subjects
    left join test_queue
       on subjects.subject_id=test_queue.subject_id
  group by subjects.subject_id, subjects.subject_name
  order by subjects.subject_name

Answer 3

something like this? (you may need to cast to INT if priority_flag or locked is of a type that SUM doesn't like)

SELECT
  subject_ID
, subject_name
, SUM(priority_flag) AS priority_count
, SUM(locked) AS locked_count
FROM Subjects s, Test_Queue tq
GROUP BY subject_ID, subject_name
ORDER BY subject_name
;

Answer 4

I'll take a punt that Test_Queue.Subject_ID = Subjects.Subject_ID

SELECT s.subject_ID, s.subject_name, COUNT(t1.*) as priority_count, COUNT(t2.*) as locked_count
  FROM Subjects s
  LEFT OUTER JOIN Test_Queue t1
    ON s.Subject_ID = t1.Subject_ID
   AND t1.priority_flag = 1
  LEFT OUTER JOIN Test_Queue t2
    ON s.Subject_ID = t2.Subject_ID
   AND t2.locked = 1
 GROUP by s.subject_ID, s.subject_name
 ORDER BY s.subject_name