统计sql查询

如@jarlh所建议的那样，MCVE会更有帮助，但在线条之间阅读，我认为你正在寻找这样的东西。您需要按状态和月份进行分组，以报告您的12个月中的每一个月。

    
      SELECT status
      ,DATE_PART(‘MONTH’, created_date )
      ,count(*) as month_count
  FROM Profile
— WHERE DATE_PART(‘MONTH’, created_date ) = ‘6’
GROUP BY status
        ,DATE_PART(‘MONTH’, created_date )
</code>
  

此答案假设数据库是Postgres，基于查询的语法。

那么，你的查询并没有考虑到年份。我怀疑你真的想要这样的东西：

    
      SELECT date_trunc(‘month’, created_date) as yyyymm, status, count(*)
FROM Profile
WHERE created_date >= date_trunc(‘month’, now())
GROUP BY yyyymm, status;
</code>
  

    
      SELECT date_trunc(‘month’, created_date) as yyyymm,
       COUNT() FILTER (WHERE status = :status1) as status_1,
       COUNT() FILTER (WHERE status = :status2) as status_2,
       COUNT(*) FILTER (WHERE status = :status3) as status_3
FROM Profile
WHERE created_date >= date_trunc(‘month’, now())
GROUP BY yyyymm;
</code>