CODEWITHSUNDEEP
sqlsql-servergroup-by
pauLo_0liveira
pauLo_0liveira

Reputation: 75

Group by query with rollup

I'm trying to make a query that shows me every region and then make a count to see how mano contracts I have per region, but somehow I'm getting an error on ROLLUP (BO2.area)

This is the query

SELECT
  CASE WHEN BO2.area IS NULL THEN ISNULL(BO2.area, 'TOTAL') ELSE BO2.area END Area,
  COUNT(BO.status = 'INSTALLED') Contracts
FROM
  BO2
  JOIN BO ON BO.bostamp = BO2.bo2stamp
GROUP BY
  ROLLUP (BO2.area)

Upvotes: 0

Views: 59

Answers (2)

eshirvana
eshirvana

Reputation: 24568

your query fails because Area is number (I'm guessing) and you want to combine it to text , so you need to cast it to string:

SELECT
  CASE WHEN GROUPING(BO2.area)=1 THEN (cast(BO2.area as varchar(100)),'TOTAL') ELSE BO2.area END as Area
  , COUNT(CASE WHEN BO.status = 'INSTALLED' THEN 1 ELSE 0 END ) Contracts
FROM
  BO2
  JOIN BO ON BO.bostamp = BO2.bo2stamp
GROUP BY
  ROLLUP (BO2.area)

Upvotes: 0

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89091

Should use GROUPING instead of a null check, and inside the aggregate you need a CASE expression. So:

SELECT
  CASE WHEN GROUPING(BO2.area)=1 THEN 'TOTAL' ELSE BO2.area END Area,
  SUM( CASE WHEN BO.status = 'INSTALLED' THEN 1 ELSE 0 END ) Contracts
FROM
  BO2
  JOIN BO ON BO.bostamp = BO2.bo2stamp
GROUP BY
  ROLLUP (BO2.area)

Upvotes: 1

Related Questions