CREATE TABLE IF NOT EXISTS `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`form_id` tinyint(4) NOT NULL COMMENT '1=shortfor;2=longfom',
`remote_addr` varchar(19) NOT NULL,
`type` tinyint(4) NOT NULL COMMENT '1=impression;2=click;3=conversion',
`website_url` varchar(50) NOT NULL,
`c_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `form_id` (`form_id` , `remote_addr` , `type` , `website_url`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=64;
i want to total count impression,click,conversion. i want group by website url group by impression,group by click ,group by conversion. how to wright query? please help me i need to emergency !!!!! ![enter image description here][1]
I need following result
website url----------Impression----click--converison-----
192.1.1.1--------------1-----------2------1--------------
192.1.1.2--------------3-----------2------2--------------
192.1.1.3--------------4-----------3------1--------------
192.1.1.4--------------2-----------6------1--------------
please try below query
$sql = "SELECT website_url,SUM(type=1) as impression,SUM(type=2) as click,SUM(type=3) as conversion FROM `test` GROUP BY website_url";
Let me know this is the way you need.