Get a count at contacttitle but get 87 row need 22 rows

Question

Get a count at contacttitle but get 87 row need 22 rows

GG_BI_GG

SSCrazy

Points: 2627
More actions
July 24, 2018 at 12:54 am

#354936

select
Country
, Contacttitle
, COUNT(ContactTitle) as Count
,CASE WHEN GROUPING_ID() = 0 THEN ''
WHEN GROUPING_ID() = 1 THEN 'Subtotal for << Country >>'
END AS Legend
from dbo.customers
group by ROLLUP(country, ContactTitle)
What im missing in the syntax.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

saravanatn SSCarpal Tunnel Points: 4533 More actions · Answer 1

GG_BI_GG - Tuesday, July 24, 2018 12:54 AM
select
Country
, Contacttitle
, COUNT(ContactTitle) as Count
,CASE WHEN GROUPING_ID() = 0 THEN ''
WHEN GROUPING_ID() = 1 THEN 'Subtotal for << Country >>'
END AS Legend
from dbo.customers
group by ROLLUP(country, ContactTitle)
What im missing in the syntax.

Kindly post at least minimum information to answer your question.

Saravanan

sgmunson SSC Guru Points: 110640 More actions · Answer 2

GG_BI_GG - Tuesday, July 24, 2018 12:54 AM
select
Country
, Contacttitle
, COUNT(ContactTitle) as Count
,CASE WHEN GROUPING_ID() = 0 THEN ''
WHEN GROUPING_ID() = 1 THEN 'Subtotal for << Country >>'
END AS Legend
from dbo.customers
group by ROLLUP(country, ContactTitle)
What im missing in the syntax.

We don't have your tables to work with, and you haven't supplied any sample data, either. What; exactly; could we possibly do to help, given nothing to work with? About the only thing I can ask is why are you doing a count on a column you are grouping by...

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

andycadley SSCertifiable Points: 5296 More actions · Answer 3

Grouping_ID doesn't work like that, you need to specify the groupings in its argument list. Although personally for such a simple case I'd just use Grouping as it's a bit easier to understand and you don't need the added complexity if you've only got a single level to distinguish between.