1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

How do I count 2 columns and then aggregate?

Discussion in 'Databases' started by Masterful, Aug 13, 2014.

  1. #1
    I have 2 simple tables:
    SEMrush

    categories
    -----------
    category_id
    category_name


    movies
    --------
    id
    movie_name
    category_id
    subcategory_id


    As you can see, the movies can be given a category and a sub-category.


    I can select the categories and count how many movies there are in each category like this:


    SELECT category_name, COUNT(id) AS Number_of_Movies
    FROM `categories`
    JOIN `movies` USING(category_id)
    GROUP BY category_id
    ORDER BY category_name ASC


    But how do I include the sub-categories in the count?
     
    Masterful, Aug 13, 2014 IP
    SEMrush
  2. Masterful

    Masterful Well-Known Member

    Messages:
    1,653
    Likes Received:
    28
    Best Answers:
    0
    Trophy Points:
    140
    #2
    Okay, I've just done the following and it seems to work:

    SELECT category_name, (SELECT COUNT(id)) + (SELECT COUNT(subcategory_id)) as Number_of_Movies
    FROM `categories`
    JOIN `movies` USING(category_id)
    GROUP BY category_id
    ORDER BY category_name ASC

    Is that the best way to do this kind of query?
     
    Masterful, Aug 13, 2014 IP
  3. Masterful

    Masterful Well-Known Member

    Messages:
    1,653
    Likes Received:
    28
    Best Answers:
    0
    Trophy Points:
    140
    #3
    Actually, I was wrong; that doesn't work.

    Any ideas?
     
    Masterful, Aug 13, 2014 IP
  4. Masterful

    Masterful Well-Known Member

    Messages:
    1,653
    Likes Received:
    28
    Best Answers:
    0
    Trophy Points:
    140
    #4
    Okay, I seem to have worked it out:

    SELECT category_name, COUNT(id) as Num
    FROM `categories`
    JOIN `movies`
    WHERE movies.category_id = categories.category_id
    OR movies.subcategory_id = categories.category_id
    GROUP BY categories.category_id
    ORDER BY categories.category_id ASC

    But is that the best way of doing these kinds of queries? Can you see any problems?
     
    Masterful, Aug 13, 2014 IP
  5. Masterful

    Masterful Well-Known Member

    Messages:
    1,653
    Likes Received:
    28
    Best Answers:
    0
    Trophy Points:
    140
    #5
    Another question . . .

    id and categories.category_id are primary keys.

    Should I make subcategory_id an index?
     
    Masterful, Aug 13, 2014 IP
  6. najilil

    najilil Active Member

    Messages:
    34
    Likes Received:
    0
    Best Answers:
    1
    Trophy Points:
    71
    #6
    do you need these column : [category/subcategory name] [number of movies] ?
    if yes, you should make query for category and then union with sub category.
     
    najilil, Aug 23, 2014 IP
  7. jestep

    jestep Prominent Member Premium Member

    Messages:
    3,661
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #7
    Looks like the most efficient way to accomplish this to me.

    Talking semantics here, but I prefer to keep the JOIN conditions out of the where clause. In this case it doesn't matter but on complex queries it makes it more difficult to understand what's going on.

    SELECT
        category_name,
        COUNT(id) AS Num
    FROM
        categories
    JOIN `movies` ON (
        movies.category_id = categories.category_id
        OR movies.subcategory_id = categories.category_id
    )
    GROUP BY
        categories.category_id
    ORDER BY
        categories.category_id ASC
    Code (markup):
     
    jestep, Sep 4, 2014 IP
  8. immu

    immu Greenhorn

    Messages:
    69
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    16
    #8
    This will work

       
    SELECT
        B.CATEGORY_NAME AS [Category],
        COUNT(A.ID) AS [NoOfMovies]
    FROM MOVIES A
    JOIN CATEGORIES B
        ON A.CATEGORY_ID = B.A.CATEGORY_ID OR A.SUBCATEGORY_ID = B.A.CATEGORY_ID
    Group By
        B.CATEGORY_NAME
    
    Code (markup):
     
    immu, Sep 4, 2014 IP
  9. Krellen

    Krellen Greenhorn

    Messages:
    38
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    18
    #9
    Yes.
     
    Krellen, Sep 17, 2014 IP
  10. Masterful

    Masterful Well-Known Member

    Messages:
    1,653
    Likes Received:
    28
    Best Answers:
    0
    Trophy Points:
    140
    #10
    Thank you for the help, guys.
     
    Masterful, Sep 20, 2014 IP