如何按月显示月份的计数 [英] how to show count for month order by month

问题描述

你可以帮我用sql语句按月和明年查找Appid计数顺序，但不计算现有的Month& year appid

can you help me with a sql statements to find Appid count order by month wise and year wise but don't count existing Month & year appid

column name     TYPE   

ID                 int 
AppID           int 
Month           int 
Year            int 

记录

Records

ID Appid Month Year

207 396 Jan 2013
250 396 Feb 2013
283 396 Mar 2013
319 396 Apr 2013
365 396 May 2013
221 451 Feb 2013
262 451 Mar 2013
293 451 Apr 2013
329 451 May 2013
384 451 June 2013
354 609 May 2013
394 702 June 2013
353 707 May 2013

我想要输出如下，

I want the output as below,

Month      count
January     1
February    1
March       0
April       0
May         2
June        1

推荐答案

可能比它需要的更复杂是。部分原因是月份和年份应存储为DATE或DATETIME数据类型，以便于数据操作。



创建测试数据表

Probably more complicated than it needs to be. Part of the reason is that Month and Year should be stored as a DATE or DATETIME Data Type for easier data manipulation.

Create the test data table

Create Table  tabledata (id int,appid int, Month int, Year Int)
insert into tabledata values(207,396,1,2013)
insert into tabledata values(250,396,2,2013)
insert into tabledata values(283,396,3,2013)
insert into tabledata values(319,396,4,2013)
insert into tabledata values(365,396,5,2013)
insert into tabledata values(221,451,2,2013)
insert into tabledata values(262,451,3,2013)
insert into tabledata values(293,451,4,2013)
insert into tabledata values(329,451,5,2013)
insert into tabledata values(384,451,6,2013)
insert into tabledata values(354,609,5,2013)
insert into tabledata values(394,702,6,2013)
insert into tabledata values(353,707,5,2013)

执行SELECT语句

注意：Where子句与DATEADD函数旨在处理多年而不仅仅是2013年的数据。

Execute the SELECT statement
Note: The Where clause with the DATEADD function is designed to handle data from multiple years not just 2013.

select year,month,count(distinct((year*100000)+(month*1000)+appid)) As [Count of AppIds] 
from tabledata t1 
where (select count(*) from tabledata t2 where
MONTH(DATEADD(m,-1,CAST(LTRIM(STR(t1.YEAR))+'-'+LTRIM(STR(t1.MONTH))+'-01' as Date)))=t2.month And
YEAR(DATEADD(m,-1,CAST(LTRIM(STR(t1.YEAR))+'-'+LTRIM(STR(t1.MONTH))+'-01' as Date)))=t2.year and
t2.appid=t1.appid)=0
group by year,month 
union
select year,month, 0 As [Count of AppIds] from tabledata t3 where
(select count(*) from tabledata t4 where
MONTH(DATEADD(m,-1,CAST(LTRIM(STR(t3.YEAR))+'-'+LTRIM(STR(t3.MONTH))+'-01' as Date)))=t4.month And
YEAR(DATEADD(m,-1,CAST(LTRIM(STR(t3.YEAR))+'-'+LTRIM(STR(t3.MONTH))+'-01' as Date)))=t4.year and
t4.appid=t3.appid)>0 and 
((t3.year*100)+t3.month) NOT IN 
(select distinct ((t5.year*100)+t5.month)
from tabledata t5 
where (select count(*) from tabledata t6 where
MONTH(DATEADD(m,-1,CAST(LTRIM(STR(t5.YEAR))+'-'+LTRIM(STR(t5.MONTH))+'-01' as Date)))=t6.month And
YEAR(DATEADD(m,-1,CAST(LTRIM(STR(t5.YEAR))+'-'+LTRIM(STR(t5.MONTH))+'-01' as Date)))=t6.year and
t5.appid=t6.appid)=0)
order by year,month 

结果

Results

year	month	Count of AppIds
2013	1	1
2013	2	1
2013	3	0
2013	4	0
2013	5	2
2013	6	1

试试这个...

Hi,

try this...

--create table with this structure.
  Create Table  tabledata (id int,appid int, Month varchar(10), Year Int);

-- insert data into the table
   insert into tabledata values(207,396,'January',2013)
   insert into tabledata values(250,396,'February',2013)
   insert into tabledata values(283,396,'March',2013)
   insert into tabledata values(319,396,'April',2013)
   insert into tabledata values(365,396,'May',2013)
   insert into tabledata values(221,451,'February',2013)
   insert into tabledata values(262,451,'March',2013)
   insert into tabledata values(293,451,'April',2013)
   insert into tabledata values(329,451,'May',2013)
   insert into tabledata values(384,451,'June',2013)
   insert into tabledata values(354,609,'May',2013)
   insert into tabledata values(394,702,'June',2013)
   insert into tabledata values(353,707,'May',2013)

-- Execute following query to get desired result set
;WITH TblCTE As (
SELECT ROW_NUMBER() OVER(PARTITION BY appid,Year order by appid) RowNum,
appid,Month,Year FROM tableData
)
SELECT  month,COUNT(appid) Count FROM (SELECT TblCTE.appid,A.month,
MONTH(CAST(A.Month + '1 2010' AS datetime)) MonthOrder FROM TblCTE
RIGHT OUTER JOIN (SELECT DISTINCT Month FROM tableData) As A
ON TblCTE.RowNum < 2  AND TblCTE.Month = A.Month) AS B
 GROUP BY B.Month , B.MonthOrder ORDER BY B.MonthOrder

    You will get required result set like this by executing above query.
  Month       Count
-----------------------
  January  1
  February 1
  March    0
  April    0
  May          2
  June     1

这篇关于如何按月显示月份的计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！