COUNT without GROUP BY

[dreampho]

Hi.

I am trying to count the number of rows returned.

I understand that I am supposed to use GROUP BY with COUNT, but I can't with this query as I simply want all rows returned which match the query.

Is it possible to COUNT without GROUP BY?

Here is my current query.

SELECT COUNT(*) AS count_result
FROM exp_judging AS jud
LEFT JOIN exp_submissions AS sub ON jud.rel_id = sub.id
WHERE sub.entry_id IN ('412,413,414,415')
AND jud.stage_3 IS NULL

Thank you.

 

[Barand]

There is no problem using COUNT() like that.

 

What are you trying to count exactly? A left join selects all rows in exp_judging, not just matching rows. If you want to count matches only, use inner join

[dreampho]

Thank you for your reply.

 

My count keeps returning 1, which is why I thought it was the GROUP BY issue.

I have changed to INNER JOIN but it is still returning 1 for the count.

 

I want to count how many rows where the entry_id in exp_submissions is one of the given entry_ids and the filed stage_3 IS NULL in the table exp_judging
 

[Barand]

Can you attach a dump of those tables and tell us what your expected count should be?

[dreampho]

 

   SELECT COUNT(*) AS count_result
   FROM exp_judging AS jud
   INNER JOIN exp_submissions AS sub ON jud.rel_id = sub.id
   WHERE sub.entry_id IN ('342,354,343,353')
   AND jud.stage_3 IS NULL  

exp_submissions

 

 

exp_judging

 

 

The query should return a count of 4, as all are valid and all have NULL as stage_3.

 

Thank you for your help.

[Barand]

Thanks for the images - they're real easy to load into a test database

[dreampho]

I wasn't familiar with the word 'dump'.

--
-- Table structure for table `exp_judging`
--

CREATE TABLE IF NOT EXISTS `exp_judging` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `rel_id` int(10) unsigned NOT NULL,
  `judge_id` int(10) unsigned DEFAULT NULL,
  `pre` int(10) unsigned DEFAULT NULL,
  `stage_1` int(10) unsigned DEFAULT NULL,
  `stage_2` int(10) unsigned DEFAULT NULL,
  `stage_3` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=217 ;

--
-- Dumping data for table `exp_judging`
--

INSERT INTO `exp_judging` (`id`, `rel_id`, `judge_id`, `pre`, `stage_1`, `stage_2`, `stage_3`) VALUES
(216, 34, 1, 1, 0, NULL, NULL),
(215, 33, 1, 1, 1, 3, NULL),
(214, 32, 1, 1, 1, 7, NULL),
(213, 36, 1, 1, 1, 1, NULL),
(212, 35, 1, 1, 1, 3, NULL),
(211, 31, 1, 1, 1, 2, NULL),
(210, 30, 1, 1, 1, 1, NULL);

-- --------------------------------------------------------

--
-- Table structure for table `exp_submissions`
--

CREATE TABLE IF NOT EXISTS `exp_submissions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `entry_id` int(10) NOT NULL,
  `member_id` int(10) NOT NULL,
  `member_group` int(10) NOT NULL,
  `category_id` int(10) NOT NULL,
  `type_id` int(10) NOT NULL,
  `portfolio_number` int(10) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=70 ;

--
-- Dumping data for table `exp_submissions`
--

INSERT INTO `exp_submissions` (`id`, `entry_id`, `member_id`, `member_group`, `category_id`, `type_id`, `portfolio_number`) VALUES
(30, 342, 1, 5, 2, 2, 0),
(31, 343, 1, 5, 1, 1, 2),
(32, 344, 1, 6, 1, 1, 1),
(33, 345, 1, 6, 1, 1, 1),
(34, 346, 1, 6, 2, 1, 1),
(35, 353, 1, 5, 1, 1, 2),
(36, 354, 1, 5, 1, 1, 2),
(37, 358, 1, 1, 1, 2, 0),
(38, 359, 1, 1, 1, 2, 0),
(39, 360, 1, 1, 1, 2, 0),
(40, 361, 1, 1, 1, 2, 0),
(41, 362, 1, 1, 1, 2, 0),
(42, 363, 1, 1, 1, 2, 0),
(43, 364, 1, 1, 1, 2, 0),
(44, 366, 1, 1, 1, 2, 0),
(45, 367, 1, 1, 1, 2, 0),
(46, 368, 1, 1, 1, 2, 0),
(47, 369, 1, 1, 1, 2, 0),
(48, 370, 1, 1, 1, 2, 0),
(49, 372, 1, 1, 1, 2, 0),
(50, 373, 1, 1, 1, 2, 0),
(51, 375, 1, 1, 1, 2, 0),
(52, 379, 1, 1, 1, 2, 0),
(53, 380, 1, 1, 1, 2, 0),
(54, 381, 1, 1, 1, 2, 0),
(55, 382, 1, 1, 1, 2, 0),
(56, 384, 1, 1, 1, 1, 2),
(57, 385, 1, 1, 1, 1, 2),
(58, 386, 1, 1, 1, 1, 2),
(59, 387, 1, 1, 1, 1, 3),
(60, 388, 1, 1, 1, 1, 3),
(61, 389, 1, 1, 1, 1, 3),
(62, 390, 1, 1, 1, 1, 3),
(63, 391, 1, 1, 1, 1, 3),
(64, 392, 1, 1, 1, 1, 3),
(65, 393, 1, 1, 1, 1, 3),
(66, 394, 1, 1, 1, 1, 3),
(67, 395, 1, 1, 1, 1, 3),
(68, 396, 1, 1, 1, 1, 3),
(69, 397, 1, 1, 1, 1, 2);

Edited October 18, 2013 by fenway
code tags

[Barand]

Don't put the entry_id list in single quotes

WHERE sub.entry_id IN (342,354,343,353)

edit:

 

'342,354,343,353' is a single string with a numeric value of 342 (ie as far as the first non-numeric character)

Edited October 14, 2013 by Barand

[dreampho]

I am using a PHP variable to enter the entry_ids.

WHERE sub.entry_id IN ('$variable')

I excluded it to show you the entry ids.

 

With the variable I need the single quotes.

 

Any ideas why I can't get a correct count?

Edited October 14, 2013 by dreampho

[Barand]

Don't put the entry_id list in single quotes

WHERE sub.entry_id IN (342,354,343,353)

edit:

 

'342,354,343,353' is a single string with a numeric value of 342 (ie as far as the first non-numeric character)

 

 

Again, lose the quotes.

WHERE sub.entry_id IN ($variable)

[DavidAM]

Barand is correct, the values in the IN phrase need to be integers, not a single string. As it is, the COUNT(*) will return 1 because the database server will convert the string to an integer and look it up.

 

ALSO, if you are using PHP to get the number of rows returned by the query, THAT value will ALWAYS be 1 (even if there are NO MATCHES). SELECT COUNT(*) ... will return one row containing the count. The count may be 10,000,000,000,l00; but there will only be ONE ROW returned. Make sure you are fetching that row and retrieving the value of the first column ("count_result").

[Barand]

 

mysql> SELECT * FROM test.exp_submissions
    -> WHERE entry_id IN (342,354,343,353);
+----+----------+-----------+--------------+-------------+---------+------------------+
| id | entry_id | member_id | member_group | category_id | type_id | portfolio_number |
+----+----------+-----------+--------------+-------------+---------+------------------+
| 30 |      342 |         1 |            5 |           2 |       2 |                0 |
| 31 |      343 |         1 |            5 |           1 |       1 |                2 |
| 35 |      353 |         1 |            5 |           1 |       1 |                2 |
| 36 |      354 |         1 |            5 |           1 |       1 |                2 |
+----+----------+-----------+--------------+-------------+---------+------------------+

But with the list in quotes

mysql> SELECT * FROM test.exp_submissions
    -> WHERE entry_id IN ('342,354,343,353');
+----+----------+-----------+--------------+-------------+---------+------------------+
| id | entry_id | member_id | member_group | category_id | type_id | portfolio_number |
+----+----------+-----------+--------------+-------------+---------+------------------+
| 30 |      342 |         1 |            5 |           2 |       2 |                0 |
+----+----------+-----------+--------------+-------------+---------+------------------+
1 row in set, 1 warning (0.00 sec)

which is why the total is 1 instead of 4