SQL Returns Multiple Counters
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.
This entry was posted in .NET, General and tagged count, list counters, multiple counters, single record, SQL.