Web Hosting Talk : Web Hosting Main Forums : Programming Discussion : Simple SQL query needs a good home

[jtrovato]

select count(priority_id=1), count(priority_id=2),count(priority_id=3) from leads where account_id =3

I want to have a query to return 3 fields within 1 record. I have leads and I want to be able to a row[0] tell me how many priority_id=1, row[1] tell me how many priority_id=2, and so on

This query doesn't work and I wanted to see what your guys thought out there for a simple way to get this info without doing 3 different queries.

John

[MarkIL]

SELECT COUNT(priority_id=1) AS p1, COUNT(priority_id=2) AS p2,COUNT(priority_id=3) AS p3 FROM leads WHERE account_id =3

and then just mysql_fetch_assoc().

[jtrovato]

Lovely, This will be great.

Thank you

[jtrovato]

it didn't work correctly?

I got the same number for all three fields and I know they are different?? Weird I will have to look into this more later today

[ScottD]

SELECT priority_id, count(*) FROM leads GROUP BY 1 ORDER BY 1

Should return a set of rows where the first column is the priority_id and the second column is the count. If you only want priority_id's 1, 2, and 3 then do this:

SELECT priority_id, count(*) FROM leads WHERE priority_id in (1, 2, 3) GROUP BY 1 ORDER BY 1

[jtrovato]

that works better, what about it returning the name of the priority, So I can have as many priorities as I want. All i have to do is check the mysql_num_rows function and do a loop

#
# Table structure for table 'leads'
#

CREATE TABLE `leads` (
`leads_id` int(10) unsigned NOT NULL auto_increment,
`leads_firstname` varchar(30) NOT NULL default '',
`leads_lastname` varchar(30) NOT NULL default '',
`leads_company` varchar(100) NOT NULL default '',
`leads_spouse` varchar(30) NOT NULL default '',
`leads_guestofhonor` varchar(30) NOT NULL default '',
`leads_address` varchar(75) NOT NULL default '',
`leads_address2` varchar(50) NOT NULL default '',
`leads_city` varchar(50) NOT NULL default '',
`leads_state` char(2) NOT NULL default '',
`leads_zip` varchar(10) NOT NULL default '',
`leads_homephone` varchar(10) NOT NULL default '',
`leads_workphone` varchar(10) NOT NULL default '',
`leads_workphoneext` varchar(5) NOT NULL default '',
`leads_cell` varchar(10) NOT NULL default '',
`leads_otherphone` varchar(10) NOT NULL default '',
`leads_otherphoneext` varchar(5) NOT NULL default '',
`leads_fax` varchar(10) NOT NULL default '',
`leads_email` varchar(128) NOT NULL default '',
`leads_comments` text NOT NULL,
`leads_datecall` date NOT NULL default '0000-00-00',
`leads_dateevent` date NOT NULL default '0000-00-00',
`eventtype_id` int(10) unsigned NOT NULL default '0',
`account_id` int(10) unsigned NOT NULL default '0',
`location_id` int(10) unsigned NOT NULL default '0',
`referraltype_id` int(10) unsigned NOT NULL default '0',
`leads_budget` decimal(8,2) NOT NULL default '000000.00',
`leads_client` int(1) NOT NULL default '0',
`priority_id` tinyint(3) unsigned zerofill NOT NULL default '001',
PRIMARY KEY (`leads_id`),
UNIQUE KEY `leads_id` (`leads_id`,`leads_email`),
KEY `leads_id_2` (`leads_id`)
) TYPE=MyISAM COMMENT='All leads first go here';



#
# Table structure for table 'priority'
#

CREATE TABLE `priority` (
`priority_id` tinyint(3) unsigned NOT NULL auto_increment,
`priority_name` char(30) NOT NULL default '',
`priority_value` tinyint(1) unsigned NOT NULL default '1',
PRIMARY KEY (`priority_id`),
UNIQUE KEY `priority_id` (`priority_id`),
KEY `priority_id_2` (`priority_id`)
) TYPE=MyISAM;

This would be the 2 tables I want to reference. basicly in the first field I would want to name of the priority, the numbe rof leads that have this priority, and then the level so I can order them. from highest to lowest.

Thanks again for your help

John