COUNT (*) but need it to include 0 values with in a month 1

kbsc · Sep 21, 2007

I need help!

I have a table named tblCases that includes records with a field dteFiled. I need a query that will Count each record for each month with in a year. The query below does this but if there are no records within a particular month then it excludes that month.

SELECT month(dteFiled) AS [Month],
COUNT(*) AS NoAsset

FROM tblCase

WHERE
YEAR(dteFiled) = 2007
AND intDeleted = 0
AND intType = 8
AND vchPerson = 'RLW'

GROUP BY month(dtePetitionFiled)
ORDER BY month(dtePetitionFiled)

Results should be
1 23
2 34
3 0
4 65
5 0
6 89
7 12
8 0
9 23
10 23
11 0
12 0

But it excludes, the months with 0 values and I need those values.

gmmastros · Sep 21, 2007

Try this...

Code:

[COLOR=blue]SELECT[/color] A.MonthNumber [COLOR=blue]AS[/color] [[COLOR=#FF00FF]Month[/color]],
       [COLOR=#FF00FF]COUNT[/color]([COLOR=blue]T[/color].dtePetitionFiled) [COLOR=blue]AS[/color] NoAsset
[COLOR=blue]FROM[/color]   (
       [COLOR=blue]Select[/color] 1 [COLOR=blue]As[/color] MonthNumber
       Union All [COLOR=blue]Select[/color] 2
       Union All [COLOR=blue]Select[/color] 3
       Union All [COLOR=blue]Select[/color] 4
       Union All [COLOR=blue]Select[/color] 5
       Union All [COLOR=blue]Select[/color] 6
       Union All [COLOR=blue]Select[/color] 7
       Union All [COLOR=blue]Select[/color] 8
       Union All [COLOR=blue]Select[/color] 9
       Union All [COLOR=blue]Select[/color] 10
       Union All [COLOR=blue]Select[/color] 11
       Union All [COLOR=blue]Select[/color] 12
       ) [COLOR=blue]As[/color] A
       [COLOR=#FF00FF]Left[/color] [COLOR=blue]Join[/color] tblCase [COLOR=blue]T[/color]
         [COLOR=blue]On[/color] A.MonthNumber = [COLOR=#FF00FF]Month[/color]([COLOR=blue]T[/color].dtePetitionFiled)
         And [COLOR=#FF00FF]YEAR[/color](dteFiled) = 2007
         AND intDeleted = 0
         AND intType = 8
         AND vchPerson = [COLOR=red]'RLW'[/color]
[COLOR=blue]GROUP[/color] [COLOR=blue]BY[/color] A.MonthNumber
[COLOR=blue]ORDER[/color] [COLOR=blue]BY[/color] A.MonthNumber

If this works for you, and you want further clarification regarding 'how it works', just let me know and I will explain.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

SQLDenis · Sep 21, 2007

One suggestion instead of

Code:

And YEAR(dteFiled) = 2007

do this

Code:

And dteFiled >= '20070101'
and dteFiled < '20080101'

It will perform much faster because it is sargable

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

kbsc · Sep 21, 2007

Awesome! That works perfect. Now let me add 2 twists to this.

(1) The month column value - can it be coded to say the Months name and year - instead of showing 1 can it be 1/2007

(2) I need another column that does the same thing as the Count(*), but each column's results is depending on intType = 7 and the other column intType = 8.

If you can do this that would be great. The left join of the months is perfect, I'll use this in more of my queries. THanks!!

gmmastros · Sep 21, 2007

Well... then... it's great, because I can do it. Unfortunately, I am more interested in whether you can do. I suggest you give it a try. if you get stuck, then post some code.

I will happily teach a person how to write code all day long. As soon as it changes from a 'teach me' to a 'do for me', I lose interest.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

onpnt · Sep 21, 2007

Hear Hear!

_{____________ signature below ______________

I am Tedward Keyboardhands!!!

You are a amateur developer until you realize all your code sucks.

Jeff Atwood}

kbsc · Sep 21, 2007

THis is the only thing I can think of, but this gives me an error on line 3 and 16. I've been working on this query all week (I just started writing SQL recently and haven't since College). I'm not sure how to do the WHERE clause for all columns and then one parameter per column.

SELECT A.MonthNumber AS [Month],
(SELECT COUNT(T.intType) from tblCase where T.intType = 7) AS NoAsset, (SELECT COUNT(T.intType) from tblCase where T.intType = 8) AS Asset,
FROM (
Select 1 As MonthNumber
Union All Select 2
Union All Select 3
Union All Select 4
Union All Select 5
Union All Select 6
Union All Select 7
Union All Select 8
Union All Select 9
Union All Select 10
Union All Select 11
Union All Select 12
) As A
Left Join tblCase T
On A.MonthNumber = Month(T.dtePetitionFiled)
And YEAR(dtePetitionFiled) = 2007
AND intDeleted = 0
AND vchTrustee = 'RLW'
GROUP BY A.MonthNumber
ORDER BY A.MonthNumber

kbsc · Sep 21, 2007

Ok so now i try to put the whole WHERE clause in for each column and I get an error: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Any Ideas? Or can you point me in the right direction or what I'm doing wrong. Thanks!!

SELECT A.MonthNumber AS [Month],
(SELECT COUNT(T.intType) from tblCase where T.intTYpe = 7
And YEAR(dtePetitionFiled) = 2007
AND intDeleted = 0
AND intType = 7
AND vchTrustee = 'RLW') AS NoAsset,

(SELECT COUNT(T.intType) from tblCase where T.intType = 8
And YEAR(dtePetitionFiled) = 2007
AND intDeleted = 0
AND intType = 7
AND vchTrustee = 'RLW') AS Asset
FROM (
Select 1 As MonthNumber
Union All Select 2
Union All Select 3
Union All Select 4
Union All Select 5
Union All Select 6
Union All Select 7
Union All Select 8
Union All Select 9
Union All Select 10
Union All Select 11
Union All Select 12
) As A
Left Join tblCase T
On A.MonthNumber = Month(T.dtePetitionFiled)

GROUP BY A.MonthNumber, T.intType
ORDER BY A.MonthNumber

kbsc · Sep 21, 2007

This is my first time using a tech forum. I do apologize and will definitely show my work. Thanks for letting me know!!

gmmastros · Sep 21, 2007

Some people may think I'm soft....

Code:

SELECT A.MonthNumber AS [Month],
       Sum(Case When intType = 7 Then 1 Else 0 End) As Type_7_Count,
       Sum(Case When intType = 8 Then 1 Else 0 End) As Type_8_Count
FROM   (
       Select 1 As MonthNumber
       Union All Select 2
       Union All Select 3
       Union All Select 4
       Union All Select 5
       Union All Select 6
       Union All Select 7
       Union All Select 8
       Union All Select 9
       Union All Select 10
       Union All Select 11
       Union All Select 12
       ) As A
       Left Join tblCase T
         On A.MonthNumber = Month(T.dtePetitionFiled)
         And YEAR(dtePetitionFiled) = 2007
         AND intDeleted = 0
         AND vchTrustee = 'RLW'
         
GROUP BY A.MonthNumber, T.intType
ORDER BY A.MonthNumber

Take a close look at this query. Notice how I am no longer using Count. When you think about it. Count is the same thing as Sum when you are always summing 1. Ex: You have seven school children. Each child has 1 apple. How many apples are there? You can count the number of children, or you could sum the number of apples. The answer is going to be the same.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

kbsc · Sep 21, 2007

I don't think you're soft, maybe hard but fair

here's the result set I get, thank you for your help, I just need to figure out how not to get the duplicate Month, I put DISTINCT in front of A.MonthNumber but that didn't do it, I really do appreciate your help I need to look through my attic for my SQL book from College (it might make my work easier) Thanks Again!!:
1 12 0
1 0 22
2 5 0
2 0 33
3 6 0
3 0 55
4 4 0
4 0 62
5 7 0
5 0 48
6 4 0
6 0 54
7 6 0
7 0 64
8 2 0
8 0 48
9 0 36
10 0 0
11 0 0
12 0 0

gmmastros · Sep 21, 2007

This should take care of it.

Code:

Select MonthNumber As [Month],
       Max(Type_7_Count) As Type_7_Count,
       Max(Type_8_Count) As Type_8_Count
From   (
       SELECT A.MonthNumber,
              Sum(Case When intType = 7 Then 1 Else 0 End) As Type_7_Count,
              Sum(Case When intType = 8 Then 1 Else 0 End) As Type_8_Count
       FROM   (
              Select 1 As MonthNumber
              Union All Select 2
              Union All Select 3
              Union All Select 4
              Union All Select 5
              Union All Select 6
              Union All Select 7
              Union All Select 8
              Union All Select 9
              Union All Select 10
              Union All Select 11
              Union All Select 12
              ) As A
              Left Join tblCase T
                On A.MonthNumber = Month(T.dtePetitionFiled)
                And YEAR(dtePetitionFiled) = 2007
                AND intDeleted = 0
                AND vchTrustee = 'RLW'
       GROUP BY A.MonthNumber, T.intType
       ) As A
Group By A.MonthNumber
ORDER BY A.MonthNumber

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

kbsc · Sep 21, 2007

OK, below is what I've done to the query. Problem: I need to pull this query based on MAX(dtePetitionFiled) and then I need to see results for the previous 12 months.

My problem is that I have month and year separated, is there a way to have mm/yy combined so that they query will work better. THanks!!

DECLARE @year INT
DECLARE @month INT
DECLARE @maxDtePetitionFiled DATETIME

SET @maxDtePetitionFiled = (SELECT MAX(dtePetitionFiled) FROM tblCase WHERE vchTrustee = 'RLW')
SET @year = (YEAR(@maxDtePetitionFiled))
SET @month = (MONTH(@maxDtePetitionFiled))

Select MonthNumber As [Month],
@year As Year,
Max(Asset) As Asset,
Max(No_Asset) As No_Asset
From (
SELECT A.MonthNumber,
Sum(Case When intType = 7 Then 1 Else 0 End) As Asset,
Sum(Case When intType = 8 Then 1 Else 0 End) As No_Asset
FROM (
Select @month As MonthNumber
Union All Select
(CASE
WHEN (@month - 1) = 0 THEN 12
WHEN (@month - 1) = -1 THEN 11
WHEN (@month - 1) = -2 THEN 10
WHEN (@month - 1) = -3 THEN 9
WHEN (@month - 1) = -2 THEN 8
WHEN (@month - 1) = -2 THEN 7
WHEN (@month - 1) = -2 THEN 6
WHEN (@month - 1) = -2 THEN 5
WHEN (@month - 1) = -2 THEN 4
WHEN (@month - 1) = -2 THEN 3
WHEN (@month - 1) = -2 THEN 2
WHEN (@month - 1) = -2 THEN 1
ELSE (@month - 1) END)
Union All Select
(CASE
WHEN (@month - 2) = 0 THEN 12
WHEN (@month - 2) = -1 THEN 11
WHEN (@month - 2) = -2 THEN 10
WHEN (@month - 2) = -3 THEN 9
WHEN (@month - 2) = -2 THEN 8
WHEN (@month - 2) = -2 THEN 7
WHEN (@month - 2) = -2 THEN 6
WHEN (@month - 2) = -2 THEN 5
WHEN (@month - 2) = -2 THEN 4
WHEN (@month - 2) = -2 THEN 3
WHEN (@month - 2) = -2 THEN 2
WHEN (@month - 2) = -2 THEN 1
ELSE (@month - 2) END)
Union All Select
(CASE
WHEN (@month - 3) = 0 THEN 12
WHEN (@month - 3) = -1 THEN 11
WHEN (@month - 3) = -2 THEN 10
WHEN (@month - 3) = -3 THEN 9
WHEN (@month - 3) = -2 THEN 8
WHEN (@month - 3) = -2 THEN 7
WHEN (@month - 3) = -2 THEN 6
WHEN (@month - 3) = -2 THEN 5
WHEN (@month - 3) = -2 THEN 4
WHEN (@month - 3) = -2 THEN 3
WHEN (@month - 3) = -2 THEN 2
WHEN (@month - 3) = -2 THEN 1
ELSE (@month - 3) END)
Union All Select
(CASE
WHEN (@month - 4) = 0 THEN 12
WHEN (@month - 4) = -1 THEN 11
WHEN (@month - 4) = -2 THEN 10
WHEN (@month - 4) = -3 THEN 9
WHEN (@month - 4) = -2 THEN 8
WHEN (@month - 4) = -2 THEN 7
WHEN (@month - 4) = -2 THEN 6
WHEN (@month - 4) = -2 THEN 5
WHEN (@month - 4) = -2 THEN 4
WHEN (@month - 4) = -2 THEN 3
WHEN (@month - 4) = -2 THEN 2
WHEN (@month - 4) = -2 THEN 1
ELSE (@month - 4) END)
Union All Select
(CASE
WHEN (@month - 5) = 0 THEN 12
WHEN (@month - 5) = -1 THEN 11
WHEN (@month - 5) = -2 THEN 10
WHEN (@month - 5) = -3 THEN 9
WHEN (@month - 5) = -2 THEN 8
WHEN (@month - 5) = -2 THEN 7
WHEN (@month - 5) = -2 THEN 6
WHEN (@month - 5) = -2 THEN 5
WHEN (@month - 5) = -2 THEN 4
WHEN (@month - 5) = -2 THEN 3
WHEN (@month - 5) = -2 THEN 2
WHEN (@month - 5) = -2 THEN 1
ELSE (@month - 5) END)
Union All Select
(CASE
WHEN (@month - 6) = 0 THEN 12
WHEN (@month - 6) = -1 THEN 11
WHEN (@month - 6) = -2 THEN 10
WHEN (@month - 6) = -3 THEN 9
WHEN (@month - 6) = -2 THEN 8
WHEN (@month - 6) = -2 THEN 7
WHEN (@month - 6) = -2 THEN 6
WHEN (@month - 6) = -2 THEN 5
WHEN (@month - 6) = -2 THEN 4
WHEN (@month - 6) = -2 THEN 3
WHEN (@month - 6) = -2 THEN 2
WHEN (@month - 6) = -2 THEN 1
ELSE (@month - 6) END)
Union All Select
(CASE
WHEN (@month - 7) = 0 THEN 12
WHEN (@month - 7) = -1 THEN 11
WHEN (@month - 7) = -2 THEN 10
WHEN (@month - 7) = -3 THEN 9
WHEN (@month - 7) = -2 THEN 8
WHEN (@month - 7) = -2 THEN 7
WHEN (@month - 7) = -2 THEN 6
WHEN (@month - 7) = -2 THEN 5
WHEN (@month - 7) = -2 THEN 4
WHEN (@month - 7) = -2 THEN 3
WHEN (@month - 7) = -2 THEN 2
WHEN (@month - 7) = -2 THEN 1
ELSE (@month - 7) END)
Union All Select
(CASE
WHEN (@month - 8) = 0 THEN 12
WHEN (@month - 8) = -1 THEN 11
WHEN (@month - 8) = -2 THEN 10
WHEN (@month - 8) = -3 THEN 9
WHEN (@month - 8) = -2 THEN 8
WHEN (@month - 8) = -2 THEN 7
WHEN (@month - 8) = -2 THEN 6
WHEN (@month - 8) = -2 THEN 5
WHEN (@month - 8) = -2 THEN 4
WHEN (@month - 8) = -2 THEN 3
WHEN (@month - 8) = -2 THEN 2
WHEN (@month - 8) = -2 THEN 1
ELSE (@month - 8) END)
Union All Select
(CASE
WHEN (@month - 9) = 0 THEN 12
WHEN (@month - 9) = -1 THEN 11
WHEN (@month - 9) = -2 THEN 10
WHEN (@month - 9) = -3 THEN 9
WHEN (@month - 9) = -2 THEN 8
WHEN (@month - 9) = -2 THEN 7
WHEN (@month - 9) = -2 THEN 6
WHEN (@month - 9) = -2 THEN 5
WHEN (@month - 9) = -2 THEN 4
WHEN (@month - 9) = -2 THEN 3
WHEN (@month - 9) = -2 THEN 2
WHEN (@month - 9) = -2 THEN 1
ELSE (@month - 9) END)
Union All Select
(CASE
WHEN (@month - 10) = 0 THEN 12
WHEN (@month - 10) = -1 THEN 11
WHEN (@month - 10) = -2 THEN 10
WHEN (@month - 10) = -3 THEN 9
WHEN (@month - 10) = -2 THEN 8
WHEN (@month - 10) = -2 THEN 7
WHEN (@month - 10) = -2 THEN 6
WHEN (@month - 10) = -2 THEN 5
WHEN (@month - 10) = -2 THEN 4
WHEN (@month - 10) = -2 THEN 3
WHEN (@month - 10) = -2 THEN 2
WHEN (@month - 10) = -2 THEN 1
ELSE (@month - 10) END)
Union All Select
(CASE
WHEN (@month - 11) = 0 THEN 12
WHEN (@month - 11) = -1 THEN 11
WHEN (@month - 11) = -2 THEN 10
WHEN (@month - 11) = -3 THEN 9
WHEN (@month - 11) = -2 THEN 8
WHEN (@month - 11) = -2 THEN 7
WHEN (@month - 11) = -2 THEN 6
WHEN (@month - 11) = -2 THEN 5
WHEN (@month - 11) = -2 THEN 4
WHEN (@month - 11) = -2 THEN 3
WHEN (@month - 11) = -2 THEN 2
WHEN (@month - 11) = -2 THEN 1
ELSE (@month - 11) END)
) As A
Left Join tblCase T
On A.MonthNumber = Month(T.dtePetitionFiled)
And YEAR(dtePetitionFiled) = @year
AND intDeleted = 0
AND vchTrustee = 'RLW'
GROUP BY A.MonthNumber, T.intType
) As A
Group By A.MonthNumber

kbsc · Sep 21, 2007

Here's the results of the query right now, for month 10, 11, and 12 it should be pulling for year 2006 (but of course its pulling for 2007)

1 2007 12 22
2 2007 5 33
3 2007 6 55
4 2007 4 62
5 2007 7 48
6 2007 4 54
7 2007 6 64
8 2007 2 48
9 2007 0 36
10 2007 0 0
11 2007 0 0
12 2007 0 0

kbsc · Sep 24, 2007

OK here's my new query that does everything I need it to do! Thank you so much for everyones help, in particular George.

The query examines a MAX date and then selects data based on that date going back 12 months.

DECLARE @maxDtePetitionFiled DATETIME
DECLARE @first DATETIME
DECLARE @second DATETIME
DECLARE @third DATETIME
DECLARE @fourth DATETIME
DECLARE @fifth DATETIME
DECLARE @sixth DATETIME
DECLARE @seventh DATETIME
DECLARE @eighth DATETIME
DECLARE @ninth DATETIME
DECLARE @tenth DATETIME
DECLARE @eleventh DATETIME
DECLARE @twelve DATETIME
DECLARE @yearago DATETIME

SET @maxDtePetitionFiled = (SELECT MAX(dtePetitionFiled) FROM tblCase WHERE vchTrustee = 'RLW' AND intDeleted ='0')
SET @yearago = (dateAdd(year,-1,@maxDtePetitionFiled))
SET @first = (dateAdd(month,1,@yearago))
SET @second = (dateAdd(month,2,@yearago))
SET @third = (dateAdd(month,3,@yearago))
SET @fourth = (dateAdd(month,4,@yearago))
SET @fifth = (dateAdd(month,5,@yearago))
SET @sixth = (dateAdd(month,6,@yearago))
SET @seventh = (dateAdd(month,7,@yearago))
SET @eighth = (dateAdd(month,8,@yearago))
SET @ninth = (dateAdd(month,9,@yearago))
SET @tenth = (dateAdd(month,10,@yearago))
SET @eleventh = (dateAdd(month,11,@yearago))
SET @twelve = (dateAdd(month,12,@yearago))

Select convert(varchar, MonthRange, 1) [MonthRange],
Max(Asset) As Asset,
Max(No_Asset) As No_Asset
From (
SELECT A.MonthRange,
Sum(Case When intType = 7 Then 1 Else 0 End) As Asset,
Sum(Case When intType = 8 Then 1 Else 0 End) As No_Asset
FROM (
Select @first As MonthRange
Union All Select @second
Union All Select @third
Union All Select @fourth
Union All Select @fifth
Union All Select @sixth
Union All Select @seventh
Union All Select @eighth
Union All Select @ninth
Union All Select @tenth
Union All Select @eleventh
Union All Select @twelve
) As A
Left Join tblCase T
On Month(A.MonthRange) = Month(T.dtePetitionFiled)
And YEAR(A.MonthRange) = YEAR(T.dtePetitionFiled)
AND intDeleted = 0
AND vchTrustee = 'RLW'
GROUP BY A.MonthRange, T.intType
) As A
Group By A.MonthRange

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

COUNT (*) but need it to include 0 values with in a month 1

kbsc

Technical User

gmmastros

Programmer

SQLDenis

Programmer

kbsc

Technical User

gmmastros

Programmer

onpnt

Programmer

kbsc

Technical User

kbsc

Technical User

kbsc

Technical User

gmmastros

Programmer

kbsc

Technical User

gmmastros

Programmer

kbsc

Technical User

kbsc

Technical User

kbsc

Technical User

Similar threads

Part and Inventory Search

Sponsor