[Resolved] mysql query, multiple COUNT(*) prob

[perpetualshaun]

I'm trying to do two counts, from the same table as it turns out. I think the query is pretty self-explanatory, so here it is:

SELECT proj_trck_projects.project_id, project_title, project_start_date, project_delivery_date,
accounts.account_name, contacts.contact_fname, contacts.contact_lname,
COUNT(bugs.item_id) AS bug_count, COUNT(features.item_id) AS feature_count
FROM proj_trck_projects
LEFT JOIN accounts ON accounts.account_id=proj_trck_projects.account_id
LEFT JOIN contacts ON contacts.contact_id=proj_trck_projects.contact_id
LEFT JOIN proj_trck_items AS bugs ON proj_trck_projects.project_id=bugs.project_id AND bugs.item_category='bug'
LEFT JOIN proj_trck_items AS features ON proj_trck_projects.project_id=features.project_id AND features.item_category='feature'
WHERE 1 GROUP BY proj_trck_projects.project_id ORDER BY project_title ASC LIMIT 0,50

I currently have 2 bugs and 1 feature in my proj_trck_items table, but for some reason when I run this query, I'm getting results back w/ 2 in BOTH the bug_count and feature_count column. If one is zero and the other is greater than 0 it's showing the right numbers. Otherwise, the numbers are jacked up.

Any ideas?

[Sxooter]

Basically, count(fieldname) counts the number of non-null occurrances of said field. So, if you've got anything other than NULL in those fields, it will count it. What's in that field?

[perpetualshaun]

Well right now, the COUNT is couting the ID numbers. But I thought since I added that extra parameter to each JOIN statement:

LEFT JOIN proj_trck_items AS bugs ON proj_trck_projects.project_id=bugs.project_id AND bugs.item_category='bug'
LEFT JOIN proj_trck_items AS features ON proj_trck_projects.project_id=features.project_id AND features.item_category='feature'

... that would filter them out and get the right counts. But obviously what doesn't work. So then I revised it to try and COUNT() something more limited. So here's my latest query:

SELECT proj_trck_projects.*, COUNT(bugs.item_category='bug') AS bug_count, COUNT(features.item_category='feature') AS feature_count
FROM proj_trck_projects
LEFT JOIN accounts ON accounts.account_id = proj_trck_projects.account_id
LEFT JOIN contacts ON contacts.contact_id = proj_trck_projects.contact_id
LEFT JOIN proj_trck_items AS bugs ON proj_trck_projects.project_id = bugs.project_id AND bugs.item_category='bug'
LEFT JOIN proj_trck_items AS features ON proj_trck_projects.project_id = features.project_id AND features.item_category='feature'
GROUP BY proj_trck_projects.project_id
ORDER BY project_title ASC

Sadly, this still isn't giving the right numbers. When I run this through PHP MyAdmin and look at the results the first record is showing up correctly w/ counts of 0 and 4 in the bug_count and feature_count respectively. But on the next record I have 3 bugs in the proj_trck_items table for that project and 1 feature, but this query is still spitting out 3 and 3 for the bug_count and feature_count.

Hmm...

[Sxooter]

Can you come up with a condensed test case and sample data to show this happening?

Are those counts you expect to be 0 referencing columns that have NULLs in them or something else?

IF you can't make a simple test case, can you show us the data that are being referenced (just a subset of them, not all of course)?

[perpetualshaun]

I am using all test data thus far, so I will simply list the tables and all that I have (w/ data) and I have modified the query. I'm trying to do a COUNT() on the tasks assigned to a given project now. Here's my query:

SELECT proj_trck_projects.project_id, project_title, project_start_date, project_delivery_date, COUNT( bugs.item_category = 'bug' ) AS bug_count, COUNT( features.item_category = 'feature' ) AS feature_count, COUNT( tasks.task_id ) AS task_count
FROM proj_trck_projects
LEFT JOIN proj_trck_items AS bugs ON proj_trck_projects.project_id = bugs.project_id AND bugs.item_category = 'bug'
LEFT JOIN proj_trck_items AS features ON proj_trck_projects.project_id = features.project_id AND features.item_category = 'feature'
LEFT JOIN proj_trck_tasks AS tasks ON proj_trck_projects.project_id = tasks.project_id
GROUP BY proj_trck_projects.project_id
ORDER BY project_title ASC
LIMIT 0 , 50

And here is the table structures / data:

DROP TABLE IF EXISTS `proj_trck_items`;
CREATE TABLE `proj_trck_items` (
  `item_id` int(11) NOT NULL auto_increment,
  `project_id` int(11) NOT NULL default '0',
  `task_id` int(11) NOT NULL default '0',
  `item_category` varchar(10) NOT NULL default '',
  PRIMARY KEY  (`item_id`)
) TYPE=MyISAM AUTO_INCREMENT=19 ;

INSERT INTO `proj_trck_items` (`item_id`, `project_id`, `task_id`, `item_category`) VALUES (5, 4, 0, 'bug');
INSERT INTO `proj_trck_items` (`item_id`, `project_id`, `task_id`, `item_category`) VALUES (8, 4, 0, 'bug');
INSERT INTO `proj_trck_items` (`item_id`, `project_id`, `task_id`, `item_category`) VALUES (9, 4, 0, 'bug');
INSERT INTO `proj_trck_items` (`item_id`, `project_id`, `task_id`, `item_category`) VALUES (11, 4, 0, 'feature');
INSERT INTO `proj_trck_items` (`item_id`, `project_id`, `task_id`, `item_category`) VALUES (12, 1, 0, 'feature');
INSERT INTO `proj_trck_items` (`item_id`, `project_id`, `task_id`, `item_category`) VALUES (18, 1, 0, 'feature');
INSERT INTO `proj_trck_items` (`item_id`, `project_id`, `task_id`, `item_category`) VALUES (16, 1, 0, 'feature');
INSERT INTO `proj_trck_items` (`item_id`, `project_id`, `task_id`, `item_category`) VALUES (17, 1, 0, 'feature');

#--------------------------------------------------------

DROP TABLE IF EXISTS `proj_trck_projects`;
CREATE TABLE `proj_trck_projects` (
  `project_id` int(11) NOT NULL auto_increment,
  `project_title` varchar(120) NOT NULL default '',
  `project_description` text NOT NULL,
  `project_start_date` varchar(14) NOT NULL default '',
  `project_delivery_date` varchar(14) NOT NULL default '',
  `project_lead_source` varchar(120) NOT NULL default '',
  `account_id` int(11) NOT NULL default '0',
  `contact_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`project_id`)
) TYPE=MyISAM AUTO_INCREMENT=5 ;

INSERT INTO `proj_trck_projects` (`project_id`, `project_title`, `project_description`, `project_start_date`, `project_delivery_date`, `project_lead_source`, `account_id`, `contact_id`)
VALUES (1, 'Test Project', 'dfjlafkjlaksjf \r\nfkafjlkaf\r\nfjkasfas\'fjasfkjlsafj af fasdjflksaf f fdjflkj fadsfj lkjflkasjf kdjf alkj dlkfj alkfj asdlkfj asdlkj asflk', '1115006400', '1117512000', 'kajfkj', 2, 7);
INSERT INTO `proj_trck_projects` (`project_id`, `project_title`, `project_description`, `project_start_date`, `project_delivery_date`, `project_lead_source`, `account_id`, `contact_id`)
VALUES (4, 'Test Project 2', 'dfjlafkjlaksjf \r\nfkafjlkaf\r\nfjkasfas\'fjasfkjlsafj af fasdjflksaf f fdjflkj fadsfj lkjflkasjf kdjf alkj dlkfj alkfj asdlkfj asdlkj asflk', '1115006400', '1117512000', 'kajfkj', 0, 4);

#--------------------------------------------------------

DROP TABLE IF EXISTS `proj_trck_tasks`;
CREATE TABLE `proj_trck_tasks` (
  `task_id` int(11) NOT NULL auto_increment,
  `project_id` int(11) NOT NULL default '0',
  `task_title` varchar(120) NOT NULL default '',
  `task_description` text NOT NULL,
  PRIMARY KEY  (`task_id`)
) TYPE=MyISAM AUTO_INCREMENT=2 ;

INSERT INTO `proj_trck_tasks` (`task_id`, `project_id`, `task_title`, `task_description`) VALUES (1, 1, 'afsadf', 'fassfasfd');

So w/ those tables and that query, my bug_count, feature_count, and task_count SHOULD be 0, 4, 1 respectively for Test Project and then 1, 3 and 0 for Test Project 2. Instead, it's spitting back 0, 4, 4 and 3, 3, 0.

Any ideas?

[Sxooter]

I think you misunderstand count(), and think you want sum() or something similar.

For instance, on a table with 1,000 rows, this:

select count(0) from onektable;

results in an output of 1000.

You might be able to use some kind of nullif function to make count work, but I think a solution based on sum would be a better choice.

[perpetualshaun]

Well, I'm pretty sure that COUNT() is the right function for me to use. For example, if I use the tables I outlined above and run this query:

SELECT proj_trck_projects.project_id, project_title, project_start_date, project_delivery_date, COUNT( bugs.item_category = 'bug' ) AS bug_count, COUNT( tasks.task_id ) AS task_count
FROM proj_trck_projects
LEFT JOIN proj_trck_items AS bugs ON proj_trck_projects.project_id = bugs.project_id AND bugs.item_category = 'bug'
LEFT JOIN proj_trck_tasks AS tasks ON proj_trck_projects.project_id = tasks.project_id
GROUP BY proj_trck_projects.project_id
ORDER BY project_title ASC
LIMIT 0 , 50

I get the CORRECT values in the bug_count and task_count column (0|1 and 3|0 in this case. But, wen I run another query to try and get the correct values for the feature_count and task_count:

SELECT proj_trck_projects.project_id, project_title, project_start_date, project_delivery_date, COUNT( features.item_category = 'bug' ) AS feature_count, COUNT( tasks.task_id ) AS task_count
FROM proj_trck_projects
LEFT JOIN proj_trck_items AS features ON proj_trck_projects.project_id = features.project_id AND features.item_category = 'feature'
LEFT JOIN proj_trck_tasks AS tasks ON proj_trck_projects.project_id = tasks.project_id
GROUP BY proj_trck_projects.project_id
ORDER BY project_title ASC
LIMIT 0 , 50

It's returning 4|4 and 1|0 instead of the 4|1 and 1|0 that I was expecting.

So I think I'm on the right track w/ COUNT(), but there's obviously something wrong w/ my query.