CODEWITHSUNDEEP
mysqlsqldatabase
dennypanther
dennypanther

Reputation: 63

group by based on part of a column value in mySql

I have a mySql table call details. details table is having a column call date which is having date like 2015-02-01. There can be multiple rows from a same date.

I want to find how many rows COUNT() it contains for each month of the year.

for example,

'568', '192.168.1.100', '790', '1.00', '2014-11-14'    
'569', '192.168.1.100', '780', '1.00', '2014-11-14'  
'699', '192.168.1.100', '780', '1.00', '2014-11-16'
'767', '192.168.1.102', '780', '1.00', '2014-12-15'   
'768', '192.168.1.102', '780', '1.00', '2014-12-15'

this should give COUNT like:

 '2014-11-' as 3
 '2014-12-' as 2

How can i do it using a sql select query.

Upvotes: 1

Views: 2511

Answers (2)

Ed King
Ed King

Reputation: 454

Convert to month and year using mysql functions, group and count:

SELECT month(calldate) as m, year(calldate) as y, count(*) 
FROM calldetails 
GROUP by m, y;

Given the additional information, I would do the following:

SELECT YEAR(detailtable.date) as y,
    MONTH(detailtable.date) as m, 
    AVG(detailtable.loadstate) as "load",
    count(detailtable.loadstate) as "count" 
FROM ruby_snmp.detailtable 
WHERE ipaddtress='192.168.1.102' and date LIKE '2014-12-%' 
GROUP BY y, m;

The format variables y and m would be disregarded, but need to be there for the group to work. You can add the FORMAT function to give the specific YYYY-MM as was mentioned in the second post, or you can handle data presentation in the program / report writer.

Upvotes: 1

Whirl Mind
Whirl Mind

Reputation: 884

Select DATE_FORMAT(My_Call_Date_Field, '%Y-%m') as MyYearAndMonth, Count(*) as MyCount
From My_Call_Details_Table Group By  DATE_FORMAT(My_Call_Date_Field, '%Y-%m')

You can also use the CAST function of MySql. 

Select substr(convert(My_Call_Date_Field, CHAR), 1, 7) as MyYearAndMonth, Count(*) From My_Call_Details_Table Group By substr(convert(My_Call_Date_Field, CHAR), 1, 7) Order By substr(convert(My_Call_Date_Field, CHAR), 1, 7)

In response to additional info specific to your case, as seen in your comment to the previous answer by Ed King: here it is : 

 SELECT DATE_FORMAT(detailtable.date, '%Y-%m') , AVG(detailtable.loadstate) as "load",count(detailtable.loadstate) as "count" from ruby_snmp.detailtable where ipaddtress='192.168.1.102' and date LIKE '2014-12-%' GROUP BY DATE_FORMAT(detailtable.date, '%Y-%m')

Upvotes: 4

Related Questions