sqlsql-serverrowcounttableau-desktop

Custom SQL Query in Tableau produces incorrect output when same query in SSMS runs as expected

I built SQL query that works as expected when I execute it in Microsoft SQL Server Management Studio, however when I use same query in Tableau (Custom SQL query), some columns (where aggregation was performed) are showing incorrect output (always 0).

Issue is in the lines of code shown here. I guess @@ROWCOUNT could be an issue.

SUM(CASE WHEN a.LocationName = 'Home' THEN @@ROWCOUNT ELSE 0 END) AS CountHomeServices,
SUM(CASE WHEN a.LocationName = 'Office' THEN @@ROWCOUNT ELSE 0 END) AS CountOfficeServices

Is there a way to make it work in Tableau?

Solution

  • Assuming you're just counting the rows with each LocationName, replace @@ROWCOUNT with 1.