SQL Returns Multiple Counters

Posted on Updated on

This SQL can be used to return multiple counters in one record.

select
    (select count(*) from Courses where FacilityId = 4) as counter1,
    (select count(*) from Facilities)                   as counter2,
    (select count(*) from TeeBoxes where CourseId=9)    as counter3,
    (select count(*) from Holes)                        as counter4
Results
+---+----------+----------+----------+----------+
|   | counter1 | counter2 | counter3 | counter4 |
+---+----------+----------+----------+----------+
| 1 | 2        | 4        | 5        | 468      |
+---+----------+----------+----------+----------+

Use the following SQL to return a list of counters.

create table #tempCounters( myCounter int )
insert into #tempCounters( myCounter )
(select count(*) from Courses where FacilityId = 4) 

insert into #tempCounters( myCounter )
 (select count(*) from Facilities)
    
insert into #tempCounters( myCounter )
 (select count(*) from TeeBoxes where CourseId=9)
    
insert into #tempCounters( myCounter )
    (select count(*) from Holes)

select * from #tempCounters
drop table #tempCounters
Results
+---+-----------+
|   | myCounter |
+---+-----------+
| 1 | 2         |
+---+-----------+
| 2 | 4         |
+---+-----------+
| 3 | 5         |
+---+-----------+
| 4 | 468       |
+---+-----------+

Here’s a link to a stackoverflow posting with more examples.

Leave a Reply

Your email address will not be published. Required fields are marked *