sql - 提取 month ，计算并将其放入列中

Question

我有查询问题。我用的是postgresql!我需要从日期中提取月份并获取月份的总和，然后将其放入列中

table qwe 
----------------------------
tipe    | dateissued
----------------------------
a      | 8/12/2013
b      | 8/12/2013 
c      | 8/12/2013
d      | 9/12/2013

我需要的结果是

----------------------------
tipe   | month | totalMonth
----------------------------
a      | 8     | 2 
b      | 8     | 2
c      | 8     | 2
d      | 9     | 2

总共“2”个月我从 8 和 9 开始

到目前为止我已经完成的查询

select tipe ,  extract(month from dateissued),
  count( extract(month from dateissued)) over() as totalMonth
from qwe
group by tipe,dateissued

http://sqlfiddle.com/#!12/fa8d4/6/0

Answer 1

您需要另一个选择来计算不同的月份:

SELECT tipe, 
       Extract(month FROM dateissued), 
       (SELECT Count(DISTINCT( Extract(month FROM dateissued) )) AS totalMonth 
        FROM   qwe) 
FROM   qwe 
GROUP  BY tipe, 
          dateissued;

SQL fiddle :SQL Fiddle