CASE statement with Subquery in SQL Server

xoshrz7s 于 2023-05-16 发布在 SQL Server

关注(0)|答案(1)|浏览(172)

Please see the SQL query I have.

select
CASE
    WHEN (SELECT COUNT(COL1) FROM FACT_ACTIVITY group BY FACT_ACTIVITY.COL2)>1
            THEN (SELECT COUNT(COL3) FROM FACT_ACTIVITY)
    WHEN (SELECT COUNT(COL1) FROM FACT_ACTIVITY group BY FACT_ACTIVITY.COL2)=1
            THEN (SELECT COUNT(COL4) FROM FACT_ACTTIVITY)
            END
,EXTRACT(year from fa.INC_START_DATETIME) AS YearOFF
,COUNT(fa.COL2) AS OffCount from FACT_ACTIVITY fa INNER JOIN
DIM_OFF_TYPE do ON fa.OFF_TYPE_ID = do.OFF_TYPE_ID
WHERE fa.OFF_TYPE_ID IN (24001) and YEAR(fa.INC_START_DATETIME) IN (2016,2017)
group by do.OFF_TYPE_DESCR,YEAR(fa.INC_START_DATETIME)

The subquery in case WHEN gives a lot of rows, but I want the subquery do the check for each FACT_ACTIVITY_ID and see if the count(col2) >1 and do the THEN statement.

sql-server

来源：https://stackoverflow.com/questions/49634696/case-statement-with-subquery-in-sql-server

1条答案

按热度按时间

e5njpo681#

For the sub-queries to return one row for each FACT_ACTIVITY_ID, you have to put that filter in the WHERE clause:

SELECT 
            CASE 
                WHEN (SELECT    COUNT(COL1) 
                      FROM      FACT_ACTIVITY fa2 
                      WHERE     fa2.FACT_ACTIVITY_ID = fa.FACT_ACTIVITY_ID 
                      GROUP BY  FACT_ACTIVITY.COL2) > 1
                    THEN (SELECT COUNT(COL3) FROM FACT_ACTIVITY) 

                WHEN (SELECT    COUNT(COL1) 
                      FROM      FACT_ACTIVITY fa2 WHERE fa2.FACT_ACTIVITY_ID = fa.FACT_ACTIVITY_ID
                      GROUP BY  FACT_ACTIVITY.COL2)=1
                        THEN (SELECT COUNT(COL4) FROM FACT_ACTTIVITY)
                END AS GiveMeAnAlias
            ,EXTRACT(year from fa.INC_START_DATETIME) AS YearOFF
            ,COUNT(fa.COL2) AS OffCount 
FROM        FACT_ACTIVITY fa 
            INNER JOIN DIM_OFF_TYPE do ON fa.OFF_TYPE_ID = do.OFF_TYPE_ID
WHERE       fa.OFF_TYPE_ID IN (24001) 
                AND YEAR(fa.INC_START_DATETIME) IN (2016,2017)
GROUP BY    do.OFF_TYPE_DESCR,
            YEAR(fa.INC_START_DATETIME)

Note the table alias to table FACT_ACTIVITY "fa2" in the sub-queries to distinguish between FACT_ACTIVITY in the subquery vs. FACT_ACTIVITY in the outer main query.

Also note the readability and maintainability with good white space and tabbing :)

赞(0）回复(0）举报 2023-05-16

我来回答

CASE statement with Subquery in SQL Server

1条答案

相关问题

热门标签

最新问答