CODEWITHSUNDEEP
sqlsql-serverdategroup-by
109221793
109221793

Reputation: 16897

Group By Month clause issue

I have a SQL query to group a number of products added to the system by month. I ran this query last week, and ran it again today. Today the results seem to be in a different order. Is it possible to run this query and have a second column showing the month?

Here is my original query:

SELECT COUNT(*)
FROM dbo.Products p
WHERE YEAR(t.CreatedDate) = '2013' 
  AND t.Deleted = 0 
  AND t.RemovedFromSale = 0
GROUP BY MONTH(t.CreatedDate)

This returns the following:

 | (No Column Name)
1| 2009
2| 161
3| 98

Ideally, I would like something like this:

 | (No Column Name) | Month Name
1| 2009             |  
2| 161              |
3| 98               |

Is this possible by altering my query?

Upvotes: 0

Views: 158

Answers (3)

Taryn
Taryn

Reputation: 247840

If you want to return the MONTH(t.CreatedDate), then you would need to include the column in your SELECT list:

SELECT COUNT(*) as Total,
  MONTH(t.CreatedDate) as Month
FROM dbo.Products p
WHERE YEAR(t.CreatedDate) = '2013' 
  AND t.Deleted = 0 
  AND t.RemovedFromSale = 0
GROUP BY MONTH(t.CreatedDate)

The MONTH() will return the month as an integer. If you want to return the Month as it's name, then you will want to use DATENAME()

SELECT COUNT(*) as Total,
  DATENAME(month, t.CreatedDate) as Month
FROM dbo.Products p
WHERE YEAR(t.CreatedDate) = '2013' 
  AND t.Deleted = 0 
  AND t.RemovedFromSale = 0
GROUP BY DATENAME(month, t.CreatedDate)

Upvotes: 4

TechDo
TechDo

Reputation: 18659

Try:

SELECT 
    COUNT(*), DATENAME(m, CreatedDate) [MonthName]
FROM dbo.Products p
WHERE YEAR(t.CreatedDate) = '2013' AND t.Deleted = 0 AND t.RemovedFromSale = 0
GROUP BY DATENAME(m, CreatedDate)

Upvotes: 1

James Culshaw
James Culshaw

Reputation: 1057

Change your SQL to

SELECT COUNT(*), MONTH(t.CreatedDate)
  FROM dbo.Products p
  WHERE YEAR(t.CreatedDate) = '2013' AND t.Deleted = 0 AND t.RemovedFromSale = 0
  GROUP BY MONTH(t.CreatedDate)

Upvotes: 2

Related Questions