How do I count the number of times a value appears in a DB table/result?

Databases
1

The following database result/table was created by joining 2 tables together using the SQL query below. What I’m also trying to do in that query is to display a third column which counts how many times a certain value is contained within both the PRIMARY_CATEGORY column and the SECONDARY_CATEGORY column. An example of the desired result can be seen in the table at the bottom of this post. Does anyone know what I’d need to do to the existing query to do this? As you can see in this fiddle, the SQL which I have at the moment isn’t displaying the desired results. (see the result in the second table in the fiddle) - is it something wrong in the WHERE clause in the COUNT subquery?

<pre>
+--------------+------------------+--------------------+
|   HEADING    | PRIMARY_CATEGORY | SECONDARY_CATEGORY |
+--------------+------------------+--------------------+
| SubCategoryA | Main Category 1  | Main Category 3    |
| SubCategoryB | Main Category 1  | Main Category 3    |
| SubCategoryC | Main Category 1  |                    |
| SubCategoryD | Main Category 1  |                    |
| SubCategoryE | Main Category 1  | Main Category 2    |
| SubCategoryF | Main Category 1  | Main Category 3    |
| SubCategoryG | Main Category 1  |                    |
| SubCategoryH | Main Category 1  |                    |
| SubCategoryI | Main Category 1  | Main Category 2    |
| SubCategoryJ | Main Category 1  |                    |
| SubCategoryK | Main Category 1  |                    |
| SubCategoryL | Main Category 2  |                    |
| SubCategoryM | Main Category 2  |                    |
| SubCategoryN | Main Category 2  |                    |
| SubCategoryO | Main Category 2  | Main Category 1    |
| SubCategoryP | Main Category 1  | Main Category 2    |
| SubCategoryQ | Main Category 4  |                    |
+--------------+------------------+--------------------+
</pre>

Here is the query I’m using at the moment, but as you can see in the fiddle here, the count column is not adding up correctly, and I’m not sure why.

 SELECT * FROM (
SELECT h.heading AS sub_category, primary_category AS main_category,
(SELECT COUNT(primary_category) FROM `headings` WHERE primary_category =h.`primary_category` ) AS `count`
		FROM AREA_TABLE a
		INNER JOIN headings h
		ON a.Heading=h.heading
        WHERE CHAR_LENGTH(primary_category)&gt;0

		UNION

		SELECT h.heading AS heading, secondary_category AS main_category,
		(SELECT COUNT(secondary_category) FROM `headings` WHERE secondary_category =h.`secondary_category` ) AS `count`
		FROM AREA_TABLE a
		INNER JOIN headings h
		ON a.Heading=h.heading
        WHERE CHAR_LENGTH(secondary_category)&gt;0

		ORDER BY main_category, sub_category ASC ) q

&lt;pre&gt;
+--------------+-----------------+-------+
| SUB_CATEGORY |  MAIN_CATEGORY  | COUNT |
+--------------+-----------------+-------+
| SubCategoryA | Main Category 1 |    13 |
| SubCategoryB | Main Category 1 |    13 |
| SubCategoryC | Main Category 1 |    13 |
| SubCategoryD | Main Category 1 |    13 |
| SubCategoryE | Main Category 1 |    13 |
| SubCategoryF | Main Category 1 |    13 |
| SubCategoryG | Main Category 1 |    13 |
| SubCategoryH | Main Category 1 |    13 |
| SubCategoryI | Main Category 1 |    13 |
| SubCategoryJ | Main Category 1 |    13 |
| SubCategoryK | Main Category 1 |    13 |
| SubCategoryO | Main Category 1 |    13 |
| SubCategoryP | Main Category 1 |    13 |
| SubCategoryE | Main Category 2 |     7 |
| SubCategoryI | Main Category 2 |     7 |
| SubCategoryP | Main Category 2 |     7 |
| SubCategoryL | Main Category 2 |     7 |
| SubCategoryM | Main Category 2 |     7 |
| SubCategoryN | Main Category 2 |     7 |
| SubCategoryO | Main Category 2 |     7 |
| SubCategoryA | Main Category 3 |     3 |
| SubCategoryB | Main Category 3 |     3 |
| SubCategoryF | Main Category 3 |     3 |
| SubCategoryQ | Main Category 4 |     1 |
+--------------+-----------------+-------+
&lt;/pre&gt;
2

based on your data you entered into your fiddle, you’ll never get your desired result if you’re displaying the heading. If you want total counts, you’ll need to take the heading out.

This will give you an accurate count for each category under a heading…if you want the total category counts, take the heading our of the SELECT and the GROUP BY portions.



-- for sub_category then category, then count....
SELECT heading AS sub_category
	 , category
	 , COUNT(*) AS total_count
  FROM (SELECT a.heading
			 , primary_category AS category
		  FROM AREA_TABLE a
		 INNER JOIN headings h ON a.heading = h.heading
		 WHERE CHAR_LENGTH(primary_category) > 0
		UNION ALL SELECT a.heading
				   , secondary_category AS category
				FROM AREA_TABLE a
			   INNER JOIN headings h ON a.heading = h.heading
			   WHERE CHAR_LENGTH(secondary_category) > 0) q
  GROUP BY heading, category

--for category, then count....
SELECT category
	 , COUNT(*) AS total_count
  FROM (SELECT primary_category AS category
		  FROM AREA_TABLE a
		 INNER JOIN headings h ON a.heading = h.heading
		 WHERE CHAR_LENGTH(primary_category) > 0
		UNION ALL SELECT secondary_category AS category
				FROM AREA_TABLE a
			   INNER JOIN headings h ON a.heading = h.heading
			   WHERE CHAR_LENGTH(secondary_category) > 0) q
  GROUP BY category
3

Hi Dave, thank you for your reply. When I put your second query into Fiddle the counts aren’t correct. I’m not sure why…do you have any idea? eg. your query says ‘Main Category 1’ appears 11 times, when it appears 13 times.

Thanks

4

That’s because three headings area missing from the AREA_TABLE table in your fiddle example… ‘SubCategoryO’, ‘SubCategoryP’, ‘SubCategoryQ’

5

Oops, thank you very much Dave, that was it. Appreciated! That all worked for the data in fiddle, so all now correct.

It seems that when I used it on my own code, the numbers were not calculating properly, in order to get the totals/counts working properly, I added ‘GROUP by heading’ twice to the sub query:

SELECT category
	 , COUNT(*) AS total_count
  FROM (SELECT a.heading
			 , primary_category AS category
		  FROM AREA_TABLE a
		 INNER JOIN headings h ON a.heading = h.heading
		 WHERE CHAR_LENGTH(primary_category) > 0
		 GROUP BY heading
		UNION ALL SELECT a.heading
				   , secondary_category AS category
				FROM AREA_TABLE a
			   INNER JOIN headings h ON a.heading = h.heading
			   WHERE CHAR_LENGTH(secondary_category) > 0
			   GROUP BY heading) q
  GROUP BY category
6

Then you’ve got dupes in your tables somewhere, because the GROUP BY essentially does the same thing as SELECT DISTINCT, but it adds the aggregate functions (SUM, COUNT, etc) along with them…