Professional Web Applications Themes

how can i use SELECT COUNT for this? - ASP Database

Hi peeps, Just a quick one (I hope!). I've got a table called questions. Each question has a wuestion_id and a relates_to. When a new question is added, its relates_to field is 0, however, if it's a reply, the relates_to field is set to the question_id of the relevant question. All works fine. Now I want to create a Top Ten table showing what is effectively the "most replied to" questions. Pseudo-sql might go something like... SELECT TOP 10 ORDER BY Count (relates_to = question_id) or something. The way I would normally do this is to open the db, get ...

  1. #1

    Default how can i use SELECT COUNT for this?

    Hi peeps,

    Just a quick one (I hope!).

    I've got a table called questions. Each question has a wuestion_id and a
    relates_to. When a new question is added, its relates_to field is 0, however,
    if it's a reply, the relates_to field is set to the question_id of the
    relevant question. All works fine.

    Now I want to create a Top Ten table showing what is effectively the "most
    replied to" questions. Pseudo-sql might go something like...

    SELECT TOP 10 ORDER BY Count (relates_to = question_id)

    or something.

    The way I would normally do this is to open the db, get a question, then get
    all its replies, count them, and order them. However, I'm sure there's a
    better way, possibly using SELECT COUNT :o)

    Any help appreciated!

    Cheers


    D
    Dan Guest

  2. #2

    Default Re: how can i use SELECT COUNT for this?



    Try this ...


    SELECT TOP 10 COUNT(*) AS myCount
    FROM myTable
    WHERE relates_to = question_id
    ORDER BY myCount




    PW Guest

  3. #3

    Default Re: how can i use SELECT COUNT for this?

    PW wrote: 

    :-)

    Which will give exactly the same result as:

    SELECT COUNT(*) AS myCount
    FROM myTable
    WHERE relates_to = question_id

    Without GROUP BY, am aggregation function will always return a single row.

    Try:

    SELECT TOP 10 question_id, Count(*) as TotalAnswered
    FROM myTable
    WHERE relates_to > 0
    GROUP BY question_id
    ORDER BY TotalAnswered DESC


    Bob Barrows

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"


    Bob Guest

  4. #4

    Default Re: how can i use SELECT COUNT for this?

    Hi guys

    Thanks for the info. I'm getting errors though :S

    Here's the ASP for the Execute...

    set topqs = dConn.Execute("SELECT TOP 10 question_id, Count(*) AS
    TotalAnswered FROM questions WHERE relates_to > 0 GROUP BY question_id ORDER
    BY TotalAnswered DESC")

    The error I'm getting is "Too few parameters. Expected 1.", and the error
    occurs on that line.

    Any help appreciated! Also, is there a good web resource for learning the
    complexities of SQL? Be useful!

    Cheers


    Dan



    "Bob Barrows [MVP]" wrote:
     
    >
    > :-)
    >
    > Which will give exactly the same result as:
    >
    > SELECT COUNT(*) AS myCount
    > FROM myTable
    > WHERE relates_to = question_id
    >
    > Without GROUP BY, am aggregation function will always return a single row.
    >
    > Try:
    >
    > SELECT TOP 10 question_id, Count(*) as TotalAnswered
    > FROM myTable
    > WHERE relates_to > 0
    > GROUP BY question_id
    > ORDER BY TotalAnswered DESC
    >
    >
    > Bob Barrows
    >
    > --
    > Microsoft MVP - ASP/ASP.NET
    > Please reply to the newsgroup. This email account is my spam trap so I
    > don't check it very often. If you must reply off-line, then remove the
    > "NO SPAM"
    >
    >
    >[/ref]
    Dan Guest

  5. #5

    Default Re: how can i use SELECT COUNT for this?

    Hi guys

    Thanks for the info. I'm getting errors though :S

    Here's the ASP for the Execute...

    set topqs = dConn.Execute("SELECT TOP 10 question_id, Count(*) AS
    TotalAnswered FROM questions WHERE relates_to > 0 GROUP BY question_id ORDER
    BY TotalAnswered DESC")

    The error I'm getting is "Too few parameters. Expected 1.", and the error
    occurs on that line.

    Any help appreciated! Also, is there a good web resource for learning the
    complexities of SQL? Be useful!

    Cheers


    Dan



    "Bob Barrows [MVP]" wrote:
     
    >
    > :-)
    >
    > Which will give exactly the same result as:
    >
    > SELECT COUNT(*) AS myCount
    > FROM myTable
    > WHERE relates_to = question_id
    >
    > Without GROUP BY, am aggregation function will always return a single row.
    >
    > Try:
    >
    > SELECT TOP 10 question_id, Count(*) as TotalAnswered
    > FROM myTable
    > WHERE relates_to > 0
    > GROUP BY question_id
    > ORDER BY TotalAnswered DESC
    >
    >
    > Bob Barrows
    >
    > --
    > Microsoft MVP - ASP/ASP.NET
    > Please reply to the newsgroup. This email account is my spam trap so I
    > don't check it very often. If you must reply off-line, then remove the
    > "NO SPAM"
    >
    >
    >[/ref]
    Dan Guest

  6. #6

    Default Re: how can i use SELECT COUNT for this?

    Dan Nash wrote: 

    You should ALWAYS test your queries in the native query tool for whatever
    database you are using (always tell us what database type and version you
    are using. It is always relevant.) BEFORE you attempt to run them from a
    client application such as ASP.
     

    This error always means that one of the field or table names you used in the
    query does not exist in your table, so double-check that. If that's not it,
    then maybe your database engine does not support using a column alias in the
    ORDER BY clause. Try:

    SELECT TOP 10 question_id, Count(*) as TotalAnswered
    FROM questions
    WHERE relates_to > 0
    GROUP BY question_id
    ORDER BY Count(*) DESC

    Bob Barrows

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"


    Bob Guest

  7. #7

    Default Re: how can i use SELECT COUNT for this?

    Hi Bob,

    That's great. It was the second one, about the field Aliases. I'm using
    Access2k3, but will upsize the db to SQLServer before launch.

    Anyway, that worked as a peice of code, but the logic is wrong - my fault!!

    What I need it to do is show me a list of questions.. "SELECT question_id,
    question_name FROM questions WHERE relates_to = 0", and order them by the
    number of replies each question has, with the highest no of replies first
    ("ORDER BY

    in here i need to say "select count(*) from questions where relates_to = the
    question_id we're on at the moment

    " DESC"

    Could I use select within select, or will the methods you've already shown
    me work?

    Thanks for your help - simple SQL is good - I'm getting very confused with
    complex SQL though - need a good book!

    Thanks for helping :o)


    Dan



    "Bob Barrows [MVP]" wrote:
     
    >
    > You should ALWAYS test your queries in the native query tool for whatever
    > database you are using (always tell us what database type and version you
    > are using. It is always relevant.) BEFORE you attempt to run them from a
    > client application such as ASP.

    >
    > This error always means that one of the field or table names you used in the
    > query does not exist in your table, so double-check that. If that's not it,
    > then maybe your database engine does not support using a column alias in the
    > ORDER BY clause. Try:
    >
    > SELECT TOP 10 question_id, Count(*) as TotalAnswered
    > FROM questions
    > WHERE relates_to > 0
    > GROUP BY question_id
    > ORDER BY Count(*) DESC
    >
    > Bob Barrows
    >
    > --
    > Microsoft MVP - ASP/ASP.NET
    > Please reply to the newsgroup. This email account is my spam trap so I
    > don't check it very often. If you must reply off-line, then remove the
    > "NO SPAM"
    >
    >
    >[/ref]
    Dan Guest

  8. #8

    Default Re: how can i use SELECT COUNT for this?

    You've lost me. I thought a question where relates_to = 0 had no replies ...

    A picture is worth a thousand words. Show us a few rows of sample data
    (enough to illustrate the problem, but only show the relevant fields)
    followed by the rows of data you want the query to return.

    Bob Barrows
    PS, yes, you can use a "select within select" aka subquery.
    Dan Nash wrote: 
    >>
    >> You should ALWAYS test your queries in the native query tool for
    >> whatever database you are using (always tell us what database type
    >> and version you are using. It is always relevant.) BEFORE you
    >> attempt to run them from a client application such as ASP.
    >> 
    >>
    >> This error always means that one of the field or table names you
    >> used in the query does not exist in your table, so double-check
    >> that. If that's not it, then maybe your database engine does not
    >> support using a column alias in the ORDER BY clause. Try:
    >>
    >> SELECT TOP 10 question_id, Count(*) as TotalAnswered
    >> FROM questions
    >> WHERE relates_to > 0
    >> GROUP BY question_id
    >> ORDER BY Count(*) DESC
    >>
    >> Bob Barrows
    >>
    >> --
    >> Microsoft MVP - ASP/ASP.NET
    >> Please reply to the newsgroup. This email account is my spam trap so
    >> I don't check it very often. If you must reply off-line, then remove
    >> the "NO SPAM"[/ref][/ref]

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"


    Bob Guest

  9. #9

    Default Re: how can i use SELECT COUNT for this?

    ah,... no, sorry, i obviously didn't explain it very well. let me try again...

    a row with a relates_to of 0 is a question
    a row with a relates_to > 0 is a reply, the relates_to being the question_id
    of the row it belongs to.

    so the data is...

    question_id relates_to
    1 0
    2 0
    3 1
    4 2
    5 1
    6 2
    7 2

    so in the above example, question_ids 1 & 2 are questions, cos they have a
    relates_to of 0.
    question_ids 3 & 5 are replies to question_id 1
    question_ids 4, 6 & 7 are replies to question_id 2

    So, the SQL statement should return the following

    question_id
    2
    1

    that is, the question_ids in DESC order of having the most replies. it
    should never show any replies in the list, only questions (where relates_to =
    0) in number of replies order.

    Hope this is clearer? I would send a pic of the db, but I can't find an
    option for attaching? (MSDN newsgroup system). Or is the txt pic what you
    meant? :o)

    Thanks for your patience mate


    Dan



    "Bob Barrows [MVP]" wrote:
     [/ref]
    >
    > --
    > Microsoft MVP - ASP/ASP.NET
    > Please reply to the newsgroup. This email account is my spam trap so I
    > don't check it very often. If you must reply off-line, then remove the
    > "NO SPAM"
    >
    >
    >[/ref]
    Dan Guest

  10. #10

    Default Re: how can i use SELECT COUNT for this?

    This query returns the questions:
    Select Question_id from questions
    WHERE response_id = 0

    This query returns the answers:
    Select Question_id,response_id from questions
    WHERE response_id > 0

    You can use these as subqueries in a join expression as follows to return
    the questions with their answers:

    SELECT q.question_id as question_id,a.question_id as answer_id
    FROM
    (Select Question_id from questions
    WHERE response_id = 0) As q left join
    (Select Question_id,response_id from questions
    WHERE response_id > 0) As a
    on q.question_id = a.response_id

    Now you need to use this as a subquery in the FROM clause of your GROUP BY
    query:

    SELECT question_id, count(*) AS TotalAnswers FROM
    (SELECT q.question_id as question_id,a.question_id as answer
    FROM
    (Select Question_id from questions
    WHERE response_id = 0) As q left join
    (Select Question_id,response_id from questions
    WHERE response_id > 0) As a
    on q.question_id = a.response_id) As q
    GROUP BY question_id
    ORDER BY count(*) DESC


    It would be simpler to use saved queries. Save the first query as
    qQuestions, and the second as qAnswers, which allows this saved query to be
    created:

    SELECT q.question_id as question_id,a.question_id as answer_id
    FROM qQuestions As q left join qAnswers As a
    on q.question_id = a.response_id

    Save this as qQuestionsWithAnswers, allowing this much simpler-looking query
    to be created:

    SELECT TOP 10 question_id, count(*) AS TotalAnswers FROM
    (SELECT q.question_id as question_id,a.question_id as answer
    FROM qQuestionsWithAnswers
    GROUP BY question_id
    ORDER BY count(*) DESC

    You can use dynamic sql to execute this, or better yet: save this query as
    qTopTenQuestions, and you can execute it from asp like this:

    set topqs=server.createobject("adodb.recordset")
    con.qTopTenQuestions topqs

    HTH,
    Bob Barrows

    Dan Nash wrote: 
    >>
    >> --
    >> Microsoft MVP - ASP/ASP.NET
    >> Please reply to the newsgroup. This email account is my spam trap so
    >> I
    >> don't check it very often. If you must reply off-line, then remove
    >> the "NO SPAM"[/ref][/ref]

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"


    Bob Guest

  11. #11

    Default Re: how can i use SELECT COUNT for this?

    Oops. Obviously I somehow changed "relates_to" into "response_id" in the my
    solutions. Please compensate for that.

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"


    Bob Guest

  12. #12

    Default Re: how can i use SELECT COUNT for this?

    > I'm using 

    So why not just start right? Develop using MSDE or Developer Edition...
    upsizing is not going to be as simple as you seem to think.

    http://www.aspfaq.com/2182
    http://www.aspfaq.com/2214

    --
    http://www.aspfaq.com/
    (Reverse address to reply.)


    Aaron Guest

Similar Threads

  1. select count(*) from <table> taking FOREVER
    By Fan Ruo Xin in forum IBM DB2
    Replies: 10
    Last Post: June 6th, 05:24 PM
  2. does "select count(*) from mytable" always do a seq scan?
    By Culley Harrelson in forum PostgreSQL / PGSQL
    Replies: 14
    Last Post: January 10th, 08:30 PM
  3. SELECT COUNT, but only on the last 100 entries
    By Bob in forum ASP Database
    Replies: 6
    Last Post: August 20th, 11:43 AM
  4. [mysql] SELECT COUNT(*) question
    By Floris van den Berg in forum PHP Development
    Replies: 6
    Last Post: September 20th, 10:36 AM
  5. a select within a count ??
    By andrewst in forum Oracle Server
    Replies: 3
    Last Post: July 1st, 01:33 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139