Networks

Ask something
Question Locked

Nested Query - Count

0 helpful
2 replies

by memphismaven

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

How can I do the following in one nested query. I am using SQL Server 2005. Thanks

SELECT Program, PRC_PROGRAM_ID
INTO #TEMP
FROM tblPayment
GROUP BY PROGRAM, PRC_PROGRAM_ID

SELECT Program, COUNT(*) FROM #TEMP GROUP BY Program HAVING COUNT(*)>1
DROP TABLE #TEMP

2 replies

by tony hopkinson

Select Program,Count(*) From
(SELECT Program, PRC_PROGRAM_ID
FROM tblPayment
GROUP BY PROGRAM, PRC_PROGRAM_ID) pgm
HAVING COUNT(*)>1

or even

Select * From
(Select Program,Count(*) as instances From
(SELECT Program, PRC_PROGRAM_ID
FROM tblPayment
GROUP BY PROGRAM, PRC_PROGRAM_ID) pgm) npgm
Where instances > 1

pgm & npgm are aliases for the query in the parentheses.

This discussion is closed to new replies.