We help IT Professionals succeed at work.

sql server 2005 - select count

HNA071252
HNA071252 asked
on
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
Comment
Watch Question

Author

Commented:
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 BBI & Reporting Analyst

Commented:
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

Author

Commented:
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 BBI & Reporting Analyst

Commented:
Weird. I'll have a look.
Simone BBI & Reporting Analyst

Commented:
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)
BI & Reporting Analyst
Commented:
Good grief, once more for luck:

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)  x

Author

Commented:
Thanks.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.