SitePoint Sponsor

User Tag List

Results 1 to 19 of 19

Hybrid View

  1. #1
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    SQL COUNT function

    I have a query that generates a list of website topics from our CMS database. In addition to what it pulls from the database, the page also shows page counts for each topic, and the counting is done in the code, not the SQL. It's worked well for a number of years.

    As time has passed, we've added a lot of content, so now I want to be able to sort by page count. So that means adding COUNT to the query.

    The old query looked like this:

    Code SQL:
    SELECT    pagetopics.topic_id
    	, pagetopics.topic_name
            , pagetopics.contact_id
            , pagetopics.office
            , pagetopics.publish
            , pagecontacts.page_name
            , pagecontacts.page_title
            , pagecontacts.contact_id AS defaultcontact
            , maindir.main_id
            , maindir.first_name & ' ' & maindir.last_name AS fullname
    FROM ( pagetopics
    LEFT JOIN pagecontacts
    	ON pagetopics.topic_id = pagecontacts.topic_id 
            )
    LEFT JOIN maindir
    	ON maindir.main_id = pagetopics.contact_id

    I've tried to tweak it like this:

    Code SQL:
    SELECT    pagetopics.topic_id
    	, pagetopics.topic_name
            , pagetopics.contact_id
            , pagetopics.office
            , pagetopics.publish
            , pagecontacts.page_name
            , pagecontacts.page_title
            , pagecontacts.contact_id AS defaultcontact
            , maindir.main_id
            , maindir.first_name & ' ' & maindir.last_name AS fullname
            , COUNT(pagecontacts.page_id) AS pages
    FROM ( pagetopics
    LEFT JOIN pagecontacts
    	ON pagetopics.topic_id = pagecontacts.topic_id 
            )
    LEFT JOIN maindir
    	ON maindir.main_id = pagetopics.contact_id
    GROUP BY pagetopics.topic_id
    	, pagetopics.topic_name
            , pagetopics.contact_id
            , pagetopics.office
            , pagetopics.publish
            , pagecontacts.page_name
            , pagecontacts.page_title
            , pagecontacts.contact_id
            , maindir.main_id
            , maindir.first_name & ' ' & maindir.last_name

    And of course my count is 1 for every topic. I think I made to do a subquery, but I'm not having any epiphanies. Any advice?
    <cfset myblog = "http://cydewaze.org/">

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cydewaze View Post
    The old query looked like this:
    could you briefly identify the 1-to-many relationships between those three tables
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Yup!

    pagetopics has a list of topics, each having a topic name and ID

    pagecontacts is a list of actual pages, and there is a topic_id column.

    Each page can have only one topic, but each topic can have as many pages as it wants.

    maindir is our staff directory. Name, ID, etc.

    Each topic in pagetopics has a default contact, so pagetopic and maindir are 1-1 on this case.
    <cfset myblog = "http://cydewaze.org/">

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    is this ms access? because you have parentheses in your FROM clause

    try this --
    Code:
    SELECT pagetopics.topic_id
         , pagetopics.topic_name
         , pagetopics.contact_id
         , pagetopics.office
         , pagetopics.publish
         , pagecontacts.page_name
         , pagecontacts.page_title
         , pagecontacts.contact_id AS defaultcontact
         , maindir.main_id
         , maindir.first_name & ' ' & maindir.last_name AS fullname
         , ( SELECT COUNT(*)
               FROM pagecontacts
              WHERE topic_id = pagetopics.topic_id ) AS topics
      FROM ( 
           pagetopics
    LEFT 
      JOIN pagecontacts
        ON pagecontacts.topic_id = pagetopics.topic_id 
           )
    LEFT 
      JOIN maindir
        ON maindir.main_id = pagetopics.contact_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    That's almost exactly like a version I tried, but I did COUNT(page_id) rather than COUNT(*) so that's probably what I did wrong.

    Thanks! It works.

    Now I have to figure out how to ORDER BY that count.
    <cfset myblog = "http://cydewaze.org/">

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cydewaze View Post
    ... that's probably what I did wrong.
    actually it was the fact that you didn't use a subselect in the SELECT clause, but rather the COUNT() with a GROUP BY in the outer query

    my query did not use a GROUP BY, but a correlated subquery instead

    Quote Originally Posted by cydewaze View Post
    Now I have to figure out how to ORDER BY that count.
    Code:
    ORDER
        BY topics DESC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    I did try a subselect, but I got an error, so I must have done something else wrong.

    And I can't order by like that, I get an error:

    "No value given for one or more required parameters."

    I think I'm not allowed to order by a column alias.
    <cfset myblog = "http://cydewaze.org/">

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cydewaze View Post
    I think I'm not allowed to order by a column alias.
    yes you are

    please show the exact query you tried it on
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Here we go.

    Code SQL:
    SELECT    pagetopics.topic_id
    	, pagetopics.topic_name
            , pagetopics.contact_id
            , pagetopics.office
            , pagetopics.publish
            , pagecontacts.page_name
            , pagecontacts.page_title
            , pagecontacts.contact_id AS defaultcontact
            , maindir.main_id
            , maindir.first_name & ' ' & maindir.last_name AS fullname
            , ( SELECT COUNT(*)
                FROM pagecontacts
                WHERE topic_id = pagetopics.topic_id ) AS pages
    FROM ( pagetopics
    LEFT JOIN pagecontacts
    	ON pagetopics.topic_id = pagecontacts.topic_id 
            )
    LEFT JOIN maindir
    	ON maindir.main_id = pagetopics.contact_id
    GROUP BY pagetopics.topic_id
    	, pagetopics.topic_name
            , pagetopics.contact_id
            , pagetopics.office
            , pagetopics.publish
            , pagecontacts.page_name
            , pagecontacts.page_title
            , pagecontacts.contact_id
            , maindir.main_id
            , maindir.first_name & ' ' & maindir.last_name 
    ORDER BY pages DESC
    <cfset myblog = "http://cydewaze.org/">

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    remove the GROUP BY clause in its entirety
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    UGH! I don't need GROUP BY because the COUNT is in a subselect!

    But I still get the same error. I wonder if it's an Access thing (yes, despite SQL Server 2008 being available, we STILL have to use Access because they haven't decided who will have access to create/modify databases).
    <cfset myblog = "http://cydewaze.org/">

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cydewaze View Post
    But I still get the same error.
    try naming the column some other name, i'm guessing pages might be a reserved word?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    I tried that (numpages) and got the same error.
    <cfset myblog = "http://cydewaze.org/">

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cydewaze View Post
    I tried that (numpages) and got the same error.
    maybe the error message is referencing some other part of the sql statement
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    I thought about that, but it works when I remove the ORDER BY and returns when I re-add it.

    Google "sql order by column alias" returns quite a few results of people experiencing the same problem.
    <cfset myblog = "http://cydewaze.org/">

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    try like this --
    Code:
    SELECT *
      FROM ( SELECT ...
                  , ( SELECT COUNT(*)
                        FROM ... ) AS pages
               FROM ... ) AS query
    ORDER
        BY pages DESC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Oh my, that sure is thinking outside the box!

    And it works!

    And as a side perk, I got you to use the evil, dreaded SELECT * !!!
    <cfset myblog = "http://cydewaze.org/">

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cydewaze View Post
    And as a side perk, I got you to use the evil, dreaded SELECT * !!!
    as with any rule, there are exceptions

    it is assumed that the nested query, which used to be your original query, lists all of the columns it wants to return, including the pages count

    therefore, there is no uncertainty about which columns the outer nesting query will receive, and thus the use of the asterisk is good

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #19
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    it is assumed that the nested query, which used to be your original query, lists all of the columns it wants to return
    Which is also probably why I haven't noticed any performance differences between the new and old queries.

    As always, thanks for the help rudy!
    <cfset myblog = "http://cydewaze.org/">


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
  •