SQL: COUNT() + JOIN() to count items per category INCLUDING empty categories

Question

so, I am having two tables. One with categories

cat_id || cat_name
   1   ||    cat1
   2   ||    cat2
   3   ||    cat3

one with items

item_id || cat_id
   1   ||    1
   2   ||    1
   3   ||    3

Now, I am trying the traditionnal count Item per category. after reading around (like here for exemple) i managed to build a query that retuns to me the number of items per category in the following format

SELECT 
    item.cat_id,
    COUNT(*) item_count
FROM
    item AS item
    JOIN cat as cat
    ON item.cat_id = cat.cat_id
GROUP BY 
    item.cat_id

----------------------
RESULT: 

----------------------
cat_id  || item_count
  1     ||      2
  3     ||      1
----------------------

So, my problem here, is that I would also love to include categories that have no object... and I have been trying to wrap my head around that i can't figure it out. Something in that format:

----------------------
cat_id  || item_count
  1     ||      2
  2     ||      0
  3     ||      1
----------------------

any clues? thank you in advance

SQL query to build exemple: 


SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


--
-- Database: `test`
--

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

--
-- Table structure for table `cat`
--

DROP TABLE IF EXISTS `cat`;
CREATE TABLE IF NOT EXISTS `cat` (
  `cat_id` int(11) NOT NULL,
  `cat_name` varchar(10) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `cat`
--

INSERT INTO `cat` (`cat_id`, `cat_name`) VALUES
(1, 'cat1'),
(2, 'cat2'),
(3, 'cat3');

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

--
-- Table structure for table `item`
--

DROP TABLE IF EXISTS `item`;
CREATE TABLE IF NOT EXISTS `item` (
  `item_id` int(11) NOT NULL,
  `cat_id` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `item`
--

INSERT INTO `item` (`item_id`, `cat_id`) VALUES
(1, 1),
(2, 1),
(3, 1);
COMMIT;

score 1 · Answer 1 · answered Dec 12 '20 at 12:14

That's a left join:

SELECT c.cat_id, COUNT(i.cat_id) AS item_count
FROM cat c
LEFT JOIN item i ON i.cat_id = c.cat_id
GROUP BY c.cat_id

Note that I used table aliases to shorten the query, and fixed irrelevant table alias online.

You can also get the same result with a correlated subquery:

SELECT c.cat_id,
    (SELECT COUNT(*) FROM item i WHERE i.cat_id = c.cat_id) AS item_count
FROM cat c

For performance, consider an index on item(cat_id) - if you have a properly declared foreign key, it is already there.

thank you for the detailed and complete answer <3 I was actually editing my post while you re were giving me the answer. — Quiche, Dec 12 '20 at 12:27

score 1 · Answer 2 · answered Dec 12 '20 at 12:16

1

A correlated subquery is a simple method:

select c.*,
       (select count(*)
        from item i
        where i.cat_id = c.cat_id
       ) as item_count
from cat c

answered Dec 12 '20 at 12:16

Gordon Linoff

1,242,037
58
646
786

score 0 · Answer 3 · answered Dec 12 '20 at 16:34

0

You can use sum:

select c1.cat_id, sum(c1.cat_id = c2.cat_id) 
from cat c1 
cross join items c2 
group by c1.cat_id

answered Dec 12 '20 at 16:34

Ajax1234

69,937
8
61
102

SQL: COUNT() + JOIN() to count items per category INCLUDING empty categories

3 Answers3