SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Hybrid View

  1. #1
    SitePoint Zealot
    Join Date
    Dec 2008
    Posts
    110
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    GROUP BY and then COUNT without a subquery?

    Is it possible to group by and then get a total count of all the rows without a subquery? I think the answer is no, but thought it was worth asking.

    This is my query at the moment, which does what I want, but has a subquery:
    Code:
    SELECT COUNT(*) AS count
            FROM (
                SELECT 1
                FROM img_categories
                LEFT JOIN categories AS node ON node.id = img_categories.categories_id,
                categories AS parent
                WHERE node.lft BETWEEN parent.lft AND parent.rgt
                AND parent.id = 16
                GROUP BY img_categories.img_id
            ) AS tbl

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by djeyewater View Post
    Is it possible to group by and then get a total count of all the rows without a subquery?
    yes

    however, the query you posted doesn't make a lot of sense -- for example, you're using GROUP BY without any aggregate functions

    perhaps you could explain what you're trying to accomplish?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Dec 2008
    Posts
    110
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your reply. You made me think about why I had that GROUP BY in there, and answer was that I didn't need it and could just as easily use DISTINCT. So now I have it with no subquery!

    Code:
    SELECT COUNT(DISTINCT img_id)
                FROM img_categories
                LEFT JOIN categories AS node ON node.id = img_categories.categories_id,
                categories AS parent
                WHERE node.lft BETWEEN parent.lft AND parent.rgt
                AND parent.id =16

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    nice job

    by thw way, when you have a WHERE condition that has to be not null (i.e. node.lft), then you want INNER JOIN, not LEFT OUTER JOIN
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    Dec 2008
    Posts
    110
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the advice!

    Dave


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
  •