CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Abhinav
Abhinav

Reputation: 353

Apply grouping to a SQL Server view

I have a view in my SQL Server database named PictureCount. I am getting data according the following query-

with result as
(SELECT CAST(Createddate AS DATE) AS StartDate, COUNT(ServicePictureID) AS TotalPicture 
 FROM PictureCount 
 WHERE ProjectID='11' AND 
 CAST(CreatedDate As DATE) BETWEEN '2014-10-31' AND '2014-12-05' 
 GROUP BY CAST(Createddate AS DATE)
)
SELECT 
   (CAST(DATEPART(YYYY, CAST(StartDate AS DATE)) as varchar) + '-' + CAST(DATEPART(MONTH ,CAST(StartDate AS DATE)) AS varchar)) AS StartDate, 
   TotalPicture 
FROM result

Data is appearing in the following format-

enter image description here

But I want data with group by StartDate. I am not able to apply GROUP BY clause. Please tell me if anyone knows about it.

Thanks in advance.

Upvotes: 0

Views: 48

Answers (2)

Deep
Deep

Reputation: 3202

Try this query :

SELECT Cast(Datepart(YEAR, StartDate) AS VARCHAR(4))
       + '-'
       + Cast(Datepart(MONTH, StartDate) AS VARCHAR(2)) AS StartDate,
       Count(ServicePictureID)                          AS TotalPicture
FROM   PictureCount
WHERE  ProjectID = '11'
       AND CreatedDate BETWEEN '2014-10-31' AND '2014-12-06'
GROUP  BY Datepart(YEAR, StartDate),Datepart(MONTH, StartDate)

Upvotes: 1

Shantanu Gupta
Shantanu Gupta

Reputation: 21188

How about this, if you can show data in yyyyMM format rather than yyyy-MM. This query has less no of functions, conversions.

SELECT Datepart(YEAR, StartDate)*100 + Datepart(MONTH, StartDate) as yyyyMM
, Count(ServicePictureID) AS TotalPicture
FROM   PictureCount
WHERE  ProjectID = '11'
       AND CreatedDate BETWEEN '2014-10-31' AND '2014-12-05'
GROUP  BY Datepart(YEAR, StartDate)*100 + Datepart(MONTH, StartDate)

Upvotes: 1

Related Questions