asked on

sql server 2005 - select count

How do you do select count from a select statement?

How do I write a select statement that would return a number or record from this select statement?

SELECT 'Check 2' as Check_point_2, Par_id, Trend_type, TypeOfContract, PlanEffDt
FROM TRP.FFS_Contracts
GROUP BY Par_id, Trend_type, TypeOfContract, PlanEffDt
HAVING count(Par_id) > 1

HNA071252

ASKER

I.e, If the above select statement return 5 records then it would give me a 5, or 0 if the above select statement return no record.

Simone B

select count x.* from
(SELECT 'Check 2' as Check_point_2, Par_id, Trend_type, TypeOfContract, PlanEffDt
FROM TRP.FFS_Contracts
GROUP BY Par_id, Trend_type, TypeOfContract, PlanEffDt
HAVING count(Par_id) > 1) x

HNA071252

ASKER

Can you please double check? got this syntax error:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '*'.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'x'.

Simone B

Weird. I'll have a look.

Simone B

Here you go, sorry 'bout that:

select COUNT(*) from
(SELECT 'Check 2' as Check_point_2, Par_id, Trend_type, TypeOfContract, PlanEffDt
FROM TRP.FFS_Contracts
GROUP BY Par_id, Trend_type, TypeOfContract, PlanEffDt
HAVING count(Par_id) > 1)

ASKER CERTIFIED SOLUTION

Simone B

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

HNA071252

ASKER

Thanks.