带有count和case语句的SQL查询

i need to find how many people have filed (fil_dt) their tax return within 60 days, withing 120 days, within 180 days, over 180 days of their filing period end date (fpe) for 3 different years (2006, 2007, 2008)

我需要找到有多少人在60天内提交(fil_dt)他们的纳税申报表,120天内,180天内,提交期限结束日期(fpe)的180天,3年不同(2006年,2007年,2008年)

the statement below will give me ALL years i need a count for each year and for each possibility.. anyway i can do this without 2 queries ?

下面的声明将给我所有年份我需要每年和每种可能性的计数..无论如何,我可以做到这一点,没有2个查询?

SELECT YEAR(A.FPE) AS "YEAR"
,CASE                                              
  WHEN DAYS(A.FIL_DT) - DAYS(A.FPE) < 60 THEN '2 ' 
  WHEN DAYS(A.FIL_DT) - DAYS(A.FPE) < 120 THEN '4 '
  WHEN DAYS(A.FIL_DT) - DAYS(A.FPE) < 180 THEN '6 '
  WHEN DAYS(A.FIL_DT) - DAYS(A.FPE) > 180 THEN '6+'
 END AS "NBR MTH"                                  
WHERE A.FPE BETWEEN '2006-01-01' AND '2008-12-31'

i need your help thanks a lot

我非常需要你的帮助

4 个解决方案

#1

then write

   SELECT YEAR(A.FPE) AS "YEAR",
       Sum(CASE WHEN DAYS(A.FIL_DT) - DAYS(A.FPE) < 60 
                  THEN 1 Else 0 End) SixtydayCount,  
       Sum(CASE WHEN DAYS(A.FIL_DT) - DAYS(A.FPE) < 120 
                  THEN 1 Else 0 End) OneTwentyDayCount,
       Sum(CASE WHEN DAYS(A.FIL_DT) - DAYS(A.FPE) < 180 
                  THEN 1  Else 0 End) OneEightyDayCount,
       Sum(CASE WHEN DAYS(A.FIL_DT) - DAYS(A.FPE) > 180 
                  THEN 1 Else 0 End)  OverOneEightyCount  
    From Table A
    WHERE A.FPE BETWEEN '2006-01-01' AND '2008-12-31'
    Group By YEAR(A.FPE)

If you want the 120 day count and the 180 day count to only include the folks who are over 60 and less than 120, etc. then,

如果您希望120天计数和180天计数仅包括60岁以上且不足120岁的人,那么,

     SELECT YEAR(A.FPE) AS "YEAR",
       Sum(CASE WHEN DAYS(A.FIL_DT) - DAYS(A.FPE) < 60 
                  THEN 1 Else 0 End) SixtydayCount,  
       Sum(CASE WHEN DAYS(A.FIL_DT) - DAYS(A.FPE) Between 60 And 119 
                  THEN 1 Else 0 End) OneTwentyDayCount,
       Sum(CASE WHEN DAYS(A.FIL_DT) - DAYS(A.FPE) Between 120 And 179 
                  THEN 1  Else 0 End) OneEightyDayCount,
       Sum(CASE WHEN DAYS(A.FIL_DT) - DAYS(A.FPE) >= 180 
                  THEN 1 Else 0 End)  OverOneEightyCount  
    From Table A
    WHERE A.FPE BETWEEN '2006-01-01' AND '2008-12-31'
    Group By YEAR(A.FPE)

#2

SELECT  CASE
        WHEN DAYS(A.FIL_DT) - DAYS(A.FPE) < 60 THEN '2 ' 
        WHEN DAYS(A.FIL_DT) - DAYS(A.FPE) < 120 THEN '4 '
        WHEN DAYS(A.FIL_DT) - DAYS(A.FPE) < 180 THEN '6 '
        WHEN DAYS(A.FIL_DT) - DAYS(A.FPE) > 180 THEN '6+'
        END AS NBR_MTH,
        y,
        COUNT(a.fpe)
FROM    (
        SELECT  2006 AS y
        UNION ALL
        SELECT  2007 AS y
        UNION ALL
        SELECT  2008 AS y
        )
LEFT JOIN
        A
ON      A.FPE >= CAST(CONCAT(y, '-01-01') AS DATETIME)
        AND a.FPE < CAST(CONCAT(y + 1, '-01-01') AS DATETIME)
GROUP BY
        y, mbr_mth

Unlike the simple GROUP BY YEAR(), this will select 0 even for the missing records (like, if there were no 6+ records in 2008)

与简单的GROUP BY YEAR()不同,即使是丢失的记录也会选择0(例如,如果2008年没有6个以上的记录)

#3

Group by the column your case statement represents:

按您的case语句所代表的列分组:

SELECT 
  YEAR(A.FPE) AS "YEAR",
  CASE                                              
    WHEN DAYS(A.FIL_DT) - DAYS(A.FPE) < 60 THEN '2 ' 
    WHEN DAYS(A.FIL_DT) - DAYS(A.FPE) < 120 THEN '4 '
    WHEN DAYS(A.FIL_DT) - DAYS(A.FPE) < 180 THEN '6 '
    WHEN DAYS(A.FIL_DT) - DAYS(A.FPE) > 180 THEN '6+'
  END AS "NBR MTH",
  COUNT(1) AS "TOTAL"
FROM Table
WHERE 
  A.FPE BETWEEN '2006-01-01' AND '2008-12-31'
GROUP BY
  "YEAR",
  "NBR MTH"

This query will give you a count of every record for each unique year/"NBR MTH" combination.

此查询将为您计算每个唯一年份/“NBR MTH”组合的每条记录。

#4

Actually, most DBMSs don't allow GROUP BY using aliases, so it must be

实际上,大多数DBMS不允许GROUP BY使用别名,所以它必须是

...
GROUP BY 
  YEAR(A.FPE),
  CASE                                              
    WHEN DAYS(A.FIL_DT) - DAYS(A.FPE) < 60 THEN '2 ' 
    WHEN DAYS(A.FIL_DT) - DAYS(A.FPE) < 120 THEN '4 '
    WHEN DAYS(A.FIL_DT) - DAYS(A.FPE) < 180 THEN '6 '
    WHEN DAYS(A.FIL_DT) - DAYS(A.FPE) > 180 THEN '6+'
  END

#1