Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2012
    Posts
    2

    Unanswered: Using COUNT with subqueries

    I'm trying to write a query that will return the name of anime and its count in a subquery.

    I have the subquery written, but I have no idea how to do the count on what I have after the subquery.

    subquery:

    select g1.anime_n
    from genre g1, genre g2
    where g1.genre = g2.genre and g1.anime_n <> g2.anime_n and g2.anime_n = 'Jin-Roh'
    UNION ALL
    select d1.anime_n
    from directed d1, directed d2
    where d1.director_n = d2.director_n and d1.anime_n <> d2.anime_n and d2.anime_n = 'Jin-Roh'
    UNION ALL
    select w1.anime_n
    from wrote w1, wrote w2
    where w1.writer_n = w2.writer_n and w1.anime_n <> w2.anime_n and w2.anime_n = 'Jin-Roh'
    UNION ALL
    select p1.anime_n
    from productof p1, productof p2
    where p1.studio_n = p2.studio_n and p1.anime_n <> p2.anime_n and p2.anime_n = 'Jin-Roh'

    example return:
    anime_n
    ---
    Ghost in the Shell
    Ghost in the Shell
    Ghost in the Shell


    What I would like is something like:
    select anime_n, COUNT(anime_n)
    from -----
    where ----
    GROUP BY anime_N

    so that I get:
    anime_n | count(anime_n)
    ---
    Ghost in the Shell | 3

    Thanks for your help!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Dinotron View Post
    What I would like is something like:
    select anime_n, COUNT(anime_n)
    from -----
    where ----
    GROUP BY anime_N
    that's exactly how you do it
    Code:
    SELECT anime_n
         , COUNT(*)
      FROM (
           -- put your UNION subquery here, inside the parentheses
           ) AS subqry
    GROUP 
        BY anime_n
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2012
    Posts
    2
    Hahah!

    Thanks a ton, I was unsure about the syntax.

Posting Permissions

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