CODEWITHSUNDEEP
sql
Will F
Will F

Reputation: 437

Summarising MONTH value

I have a simple statement that starts:

SELECT a.product, MONTH(a.saledate) AS Month, Count(*) AS Total

Which yields, for example,

Product          Month        Total

Bike               8               1000

Please can anyone advise if it's possible to add the month's name to this query and also, is it possible to get a monthly total to appear as well?

Thanks!

Upvotes: 0

Views: 41

Answers (2)

O. Jones
O. Jones

Reputation: 108841

The query in your example counts all the rows in your table, then presents that count next to a randomly chosen row's product and sale date. That's -- almost certainly -- not what you want. MySQL is quirky that way. Other DBMSs reject your example query.

If you want to display a monthly summary of product sold, here's the basic query:

 SELECT a.product, 
        LAST_DAY(a.saledate) AS month_ending,
        COUNT(*) AS Total
   FROM table a
  GROUP BY a.product, LAST_DAY(a.saledate)

The LAST_DAY() function is a great way to extract month and year from a date.

Finally, if you want to display the text name of the month, you can use the DATE_FORMAT() function to do that. %b as a format specifier gives a three-letter month name, and %M gives the full month name. So this query will do it.

 SELECT a.product, 
        LAST_DAY(a.saledate) AS month_ending,
        DATE_FORMAT(LAST_DAY(a.saledate), '%M %Y')) AS month
        COUNT(*) AS Total
   FROM table a
  GROUP BY a.product, LAST_DAY(a.saledate)

In SQL Server 2012+ you can use the EOMONTH() function in place of LAST_DAY().

In SQL Server 2008+ you can use DATENAME(mm, a.saledate) to retrieve the month name from a date.

Upvotes: 2

Whencesoever
Whencesoever

Reputation: 2306

There are two ways of getting month name

1)

SUBSTRING('JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC ', (MONTH(a.saledate) * 4) - 3, 3)

2)

DATENAME(month, a.saledate)

Some poeple say You might be using MYSQL:

Then getting month name will be:

SELECT MONTHNAME( a.saledate);

Upvotes: 2

Related Questions