CODEWITHSUNDEEP
mysql
user866933
user866933

Reputation: 335

How to use MySQL GROUP BY for drawing a graph?

I want to draw a graph accurately, Time vs Site Visits.

X axis will be 4, 8, 12, 16, 20, 24. That's increments of four hours.

Y axis total number of visits by first 4 hours, then by next four hours etc.

How can I do it using MySql? There might be some tricks using GROUP BY, but I couldn't get it. I stored all visit to my site, used unix time stamp for time.

Upvotes: 0

Views: 154

Answers (2)

xkeshav
xkeshav

Reputation: 54050

TRY

SELECT SUM( visit ) , HOUR( `time_column` ) 
FROM time_table
WHERE DATE_SUB(  `time_column` , INTERVAL 4 HOUR ) 
GROUP BY HOUR( `time_column` )

working example

CREATE TABLE IF NOT EXISTS `time_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `waqt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `visit` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

--
-- Dumping data for table `time_table`
--

INSERT INTO `time_table` (`id`, `waqt`, `visit`) VALUES
(1, '2011-07-28 13:29:04', 3),
(2, '2011-07-28 15:29:10', 4),
(3, '2011-07-28 13:45:35', 7),
(4, '2011-07-28 15:00:47', 5),
(5, '2011-07-28 14:45:03', 6),
(6, '2011-07-28 13:00:21', 3);

and then i execute per hour visit

  SELECT SUM(visit), HOUR(waqt) 
  FROM time_table    
  WHERE DATE_SUB(`waqt`,INTERVAL 1 HOUR) GROUP BY HOUR(waqt)

Upvotes: 1

Devart
Devart

Reputation: 122032

The query can be like this -

SELECT FLOOR(HOUR(FROM_UNIXTIME(unix_ts)) / 4) period, COUNT(*) visit_count_per_4_hours FROM visits_table
  WHERE DATE(FROM_UNIXTIME(unix_ts)) = DATE(NOW())
  GROUP BY period;

This query returns visits for specified day, otherwise calculation should be modified.

Upvotes: 1

Related Questions