how to show count for month order by month

Question

0.00/5 (No votes)

See more:

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

Posted 19-Jun-13 9:57am

rameshbabu.S

Updated 19-Jun-13 22:14pm

Aarti Meswania

v2

Add a Solution

Comments

joshrduncan2012 19-Jun-13 16:57pm

What have you tried so far?

damodara naidu betha 20-Jun-13 6:06am

Month is declare as int in the table .. but data of month is in varchar

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mike Meinz · Accepted Answer · 2013-06-19T12:03:00

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

SQL

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)

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

SQL

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

damodara naidu betha · Accepted Answer · 2013-06-19T22:12:00

Hi,

try this...

SQL

--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)

SQL

-- 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

SQL

    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

how to show count for month order by month

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0