Andi Andi - 25 days ago 9
SQL Question

SQL Query: each column value is an aggergation based on a different filter on a temporary table

I want to get the count of rows from a table based on a specific criteria for each column in my result table. The table itself is a temporary table.

The query below is what I want to accomplish but I don't know what is the syntax to get to it:

WITH table1 as( a huge inner joins between different tables)

SELECT *
FROM (
(
SELECT COUNT(*)
FROM table1
WHERE
column1 = 'value1' AND column2 > 0
) AS Count1,
(
SELECT COUNT(*)
FROM table1
WHERE
column1 = 'value1' AND column2 = 0
)AS Count2,
(
SELECT COUNT(*)
FROM table1
WHERE
column1 = 'value2' AND column2 > 0
)AS Count3,
(
SELECT COUNT(*)
FROM table1
WHERE
column1 = 'value2' AND column2 = 0
) AS Count4
) CountSummary


This is not the right syntax, but I am not sure how to accomplish this concept in sql server.

This is the error that I'm getting currently, if I make my second part of the query equal to:
select * from table1


Msg 252, Level 16, State 1, Line 1
Recursive common table expression 'table1' does not contain a top-level UNION ALL operator.


The huge inner join query can be run separately without any errors.

-------------------UPDATE--------------------------

The recursive error was due to naming 'table1' the same as a table inside the inner join query part, I got rid of that, and the error was gone. Then I tried the answers and both of them worked.

Answer

Perhaps something like this:-

select
    sum(case when column1 = 'value1' and column2 > 0 then 1 else 0 end) as Count1,
    sum(case when column1 = 'value1' and column2 = 0 then 1 else 0 end) as Count2,
    sum(case when column1 = 'value2' and column2 > 0 then 1 else 0 end) as Count3,
    sum(case when column1 = 'value2' and column2 = 0 then 1 else 0 end) as Count4
from table1
Comments