CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2000
user752746
user752746

Reputation: 617

Query count with multiple table group by month return zero if month is null

I need some help one getting the project count per month for all of the projectTypes per region. I've tried the following statement but it didn't return what I need. The problem with the statement below is that it only returns the projectType only if there a count for that month. I need to return zero if the month doesn't have a count. Any help is much appreciated.

SELECT r.region, pt.projectType, count(p.id) as totalCount, p.postedOn as monthCount
FROM region r cross join ProjectTypes pt left join projects p on p.regionID = r.id and pt.id = p.TypeID 
WHERE year(p.postedOn) = '2012' 
group by r.region, pt.projectType, p.postedOn 
order by r.region

Please the sample here: http://sqlfiddle.com/#!3/6680f/18

regions:
-------------------------
id  | region    |
-------------------------
1   | East      |
-------------------------
2   | MidWest   |
-------------------------
3   | West      |
-------------------------


Project Type:
-------------------------
id  | projectType   | 
-------------------------
1   | Web Desgin    |
-------------------------
2   | Database  |
-------------------------
3   | Development   |
-------------------------


Projects:
-------------------------------------------------------------------------
id  | projectName   | regionID  | projectTypeID | postedOn  |
-------------------------------------------------------------------------
1   | Project 1 | 1     | 2     | 2012-09-02    |
-------------------------------------------------------------------------
2   | Project 2 | 2     | 2     | 2012-09-02    |
-------------------------------------------------------------------------
3   | Project 3 | 1     | 1     | 2012-09-02    |
-------------------------------------------------------------------------
4   | Project 4 | 3     | 2     | 2012-09-02    |
-------------------------------------------------------------------------
5   | Project 5 | 3     | 1     | 2012-10-02    |
-------------------------------------------------------------------------
6   | Project 6 | 3     | 2     | 2012-10-02    |
-------------------------------------------------------------------------
7   | Project 7 | 3     | 3     | 2012-10-02    |
-------------------------------------------------------------------------
8   | Project 8 | 2     | 3     | 2012-10-02    |
-------------------------------------------------------------------------
9   | Project 9 | 1     | 2     | 2012-10-02    |
-------------------------------------------------------------------------
10  | Project 10    | 1     | 2     | 2012-10-02    |
-------------------------------------------------------------------------


Desired Results:
---------------------------------------------------------
Region  | project Type  | totalCount    | monthCount    |
---------------------------------------------------------
East    | Web Desgin    | 1     | September |
---------------------------------------------------------
East    | Database  | 1     | September |
---------------------------------------------------------
East    | Development   | 0     | September |
---------------------------------------------------------
Midwest | Web Desgin    | 0     | September |
---------------------------------------------------------
Midwest | Database  | 1     | September |
---------------------------------------------------------
Midwest | Development   | 0     | September |
---------------------------------------------------------
West    | Web Desgin    | 0     | September |
---------------------------------------------------------
West    | Database  | 1     | September |
---------------------------------------------------------
West    | Development   | 0     | September |
---------------------------------------------------------
East    | Web Desgin    | 0     | October   |
---------------------------------------------------------
East    | Database  | 2     | October   |
---------------------------------------------------------
East    | Development   | 0     | October   |
---------------------------------------------------------
Midwest | Web Desgin    | 0     | October   |
---------------------------------------------------------
Midwest | Database  | 0     | October   |
---------------------------------------------------------
Midwest | Development   | 1     | October   |
---------------------------------------------------------
West    | Web Desgin    | 1     | October   |
---------------------------------------------------------
West    | Database  | 1     | October   |
---------------------------------------------------------
West    | Development   | 1     | October   |
---------------------------------------------------------

Upvotes: 1

Views: 825

Answers (2)

RichardTheKiwi
RichardTheKiwi

Reputation: 107696

The WITH block defines a virtual table of months (or rather, the first days of each month) required. This extends your CROSS JOIN to give you all the months.

;WITH months(startdate) AS (
     SELECT CAST('20120901' AS date)
  UNION ALL
     SELECT dateadd(m,1,startdate)
       FROM months
      WHERE startdate < '20121001'
)

    SELECT r.region,
           pt.projectType,
           count(p.id) totalCount,
           DATENAME(Month,m.startdate) monthCount
      FROM region r
CROSS JOIN ProjectTypes pt
CROSS JOIN months m
 LEFT JOIN projects p ON p.regionID = r.id
       AND pt.id = p.TypeID
       AND p.postedOn >= m.startdate
       AND p.postedOn <  dateadd(m,1,m.startdate)
  GROUP BY r.region, pt.projectType, m.startdate
  ORDER BY m.startdate, region, projecttype
    OPTION (maxrecursion 0);

I applied this to your fiddle

For SQL Server 2000, or for performance really, create the table Months as a proper table and fill it with months from 1999 through the year 2169, e.g.

CREATE TABLE Months (
  startdate datetime -- the first day of month
    primary key
);
insert Months
select DateAdd(M,Number,'19990101')
from master..spt_values
where type='P'
GO

Then, just choose the month range you need using the condition against the months table, i.e. as below:

    SELECT r.region,
           pt.projectType,
           count(p.id) totalCount,
           DATENAME(Month,m.startdate) monthCount
      FROM region r
CROSS JOIN ProjectTypes pt
      JOIN months m on m.startdate between '20120901' and '20121001'
 LEFT JOIN projects p ON p.regionID = r.id
       AND pt.id = p.TypeID
       AND p.postedOn >= m.startdate
       AND p.postedOn <  dateadd(m,1,m.startdate)
  GROUP BY r.region, pt.projectType, m.startdate
  ORDER BY m.startdate, region, projecttype;

Updated SQL Fiddle

Upvotes: 1

Justin
Justin

Reputation: 9724

Hi My answer take all months which are in your table and make sets. 

SELECT
r.region
,pt.projectType
,(SELECT Count(*)
  FROM Projects pp
    WHERE pt.id = pp.TypeID
      AND r.id = pp.regionID
      AND DATENAME(MONTH, p.postedOn)=DATENAME(MONTH, pp.postedOn)) AS COUNT
,DATENAME(MONTH, p.postedOn) AS monthName
FROM ProjectTypes pt, region r,
(SELECT DISTINCT postedOn FROM Projects )p
ORDER BY r.region, DATENAME(MONTH, p.postedOn)

SQLFiddle example

Upvotes: 1

Related Questions