是否可以在一个查询中按不同条件计数？ [英] Is it possible to Count by diffrent condition in one query?

问题描述

我有一个 Shipments 表，该表基本上包含带有日期的Shipments数据

I have a Shipments table which basicly contains Shipments data with dates

id是整数
日期发货是 date

id    dateshipped
1     1-JAN-16
2     1-JAN-16
3     3-FEB-16
4     9-FEB-16  

我想编写一个查询，该查询基于月计算所有发货。
我应该得到的是：

I want to write a query which count all shipments based on Months. What I should get is:

Jan  Feb  March....
2     2     0

我知道我可以通过查询每一列来做到这一点，只获取该特定月份的相关行，并且

I know I can do it by having query for each column, get only relevent rows for this specific month and just count them.

如下：

Select     (Select count(*)
           from Shipments
           Where EXTRACT(YEAR FROM dateshipped)::int=2016 and EXTRACT(MONTH FROM dateshipped)::int=1 )as JAN,
           (Select count(*)
           from Shipments
           Where EXTRACT(YEAR FROM dateshipped)::int=2016 and EXTRACT(MONTH FROM dateshipped)::int=2 )as FEB

此方法有效，但是其太多相同的代码...

我想知道是否可以使用单个 FROM 语句，并且每一列都获得它自己的相关行进行计数。

I am wondring if it is possible to do it with a single FROM statment and each column get it's own relevent rows for count.

类似的东西

Select COL1,COL2,COL3...
from Shipments
Where EXTRACT(YEAR FROM dateshipped)::int=2016;

并具有以下内容：

COL1 = count only JAN records
COL2 = count only FEB records
....

也许某些月份具有 Parations 或其他解决方案？

maybe there is something with Parations on months or any other solution?

推荐答案

您需要进行数据透视查询才能完成此操作：

You need a pivot query to accomplish this:

SELECT SUM(CASE WHEN EXTRACT(MONTH FROM dateshipped)::int = 1  THEN 1 ELSE 0 END) AS Jan,
       SUM(CASE WHEN EXTRACT(MONTH FROM dateshipped)::int = 2  THEN 1 ELSE 0 END) AS Feb,
       SUM(CASE WHEN EXTRACT(MONTH FROM dateshipped)::int = 3  THEN 1 ELSE 0 END) AS Mar,
       SUM(CASE WHEN EXTRACT(MONTH FROM dateshipped)::int = 4  THEN 1 ELSE 0 END) AS Apr,
       SUM(CASE WHEN EXTRACT(MONTH FROM dateshipped)::int = 5  THEN 1 ELSE 0 END) AS May,
       SUM(CASE WHEN EXTRACT(MONTH FROM dateshipped)::int = 6  THEN 1 ELSE 0 END) AS Jun,
       SUM(CASE WHEN EXTRACT(MONTH FROM dateshipped)::int = 7  THEN 1 ELSE 0 END) AS Jul,
       SUM(CASE WHEN EXTRACT(MONTH FROM dateshipped)::int = 8  THEN 1 ELSE 0 END) AS Aug,
       SUM(CASE WHEN EXTRACT(MONTH FROM dateshipped)::int = 9  THEN 1 ELSE 0 END) AS Sep,
       SUM(CASE WHEN EXTRACT(MONTH FROM dateshipped)::int = 10 THEN 1 ELSE 0 END) AS Oct,
       SUM(CASE WHEN EXTRACT(MONTH FROM dateshipped)::int = 11 THEN 1 ELSE 0 END) AS Nov,
       SUM(CASE WHEN EXTRACT(MONTH FROM dateshipped)::int = 12 THEN 1 ELSE 0 END) AS Dec
FROM Shipments
WHERE EXTRACT(YEAR FROM dateshipped)::int=2016

这篇关于是否可以在一个查询中按不同条件计数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！