SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    Join Date
    May 2009
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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?

    Code:
    <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.
    Code:
     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)>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)>0
            
    		ORDER BY main_category, sub_category ASC ) q
    Code:
    <pre>
    +--------------+-----------------+-------+
    | 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 |
    +--------------+-----------------+-------+
    </pre>

  2. #2
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,204
    Mentioned
    106 Post(s)
    Tagged
    1 Thread(s)
    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.

    Code:
    -- 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
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style

  3. #3
    SitePoint Member
    Join Date
    May 2009
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #4
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,204
    Mentioned
    106 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by Metzed View Post
    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
    That's because three headings area missing from the AREA_TABLE table in your fiddle example... 'SubCategoryO', 'SubCategoryP', 'SubCategoryQ'
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style

  5. #5
    SitePoint Member
    Join Date
    May 2009
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    Code:
    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. #6
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,204
    Mentioned
    106 Post(s)
    Tagged
    1 Thread(s)
    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.....
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •