Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 10 of 10
  1. #1
    Regular Coder
    Join Date
    Jul 2005
    Location
    Oxfordshire, UK
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Count * and order by

    i have the following count query, which returns the number of records found for each studentid, however it would be really cool if i could order these in desc order by the total count, so that i can see the students at the top of a table with the highest count of records.

    Code:
    SELECT studentid, COUNT(*) as total FROM blue_form group by studentid

    i tried adding an order by total desc on to the end of the query but it doesnt like it...

    any other suggestions?

  • #2
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    Code:
    SELECT studentid, COUNT(*) as 'total' FROM blue_form group by studentid ORDER BY 'total' DESC
    When ORDERing BY an alias, put the Alias in Single Quotes.
    To say my fate is not tied to your fate is like saying, 'Your end of the boat is sinking.' -- Hugh Downs
    Please, if you found my post helpful, pay it forward. Go and help someone else today.

  • #3
    Regular Coder
    Join Date
    Jul 2005
    Location
    Oxfordshire, UK
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts
    when i do that though i get an error when trying to write the total

    using this to write out the total <% response.write rs1("total") %>

    error i get is:

    Source line: response.write rs1(total)
    Description: Item cannot be found in the collection corresponding to the requested name or ordinal.

  • #4
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    If you are using MSSQL or MSAccess, then try this:

    Code:
    SELECT studentid, COUNT(*) AS [total] FROM blue_form group by studentid ORDER BY 'total' DESC
    Only leave the single quotes around the ORDER BY rather than both alias and ORDER BY.

    I run a very similar query in MySQL all the time and it always works, so it must be an issue with MSSQL or MSAccess not seeing the alias as a field name in your results.
    To say my fate is not tied to your fate is like saying, 'Your end of the boat is sinking.' -- Hugh Downs
    Please, if you found my post helpful, pay it forward. Go and help someone else today.

  • #5
    Regular Coder
    Join Date
    Jul 2005
    Location
    Oxfordshire, UK
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts
    k, i can display the total with the order by in however the list is not ordered...

    Code:
    SELECT studentid, COUNT(*) as [total] FROM blue_form group by studentid order by 'total' asc


    using this to write out the total:
    Code:
    <% response.write rs1("total") %>
    i am using access btw.

  • #6
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    Would you be willing to run this code snippet?

    Code:
    <%
    arrayTotals = rs1.GetRows()
    RowCount = ubound(arrayTotals,2)
     '
    rs1.close
    '
    for x=0 to RowCount
    	response.write("Student: " & arrayTotals(0,x) & " :: Total: " & arrayTotals(1,x) & "<br />" & vbCrLf)
    next
    '
    response.flush
    %>
    The above will tell you for sure if you are ordering in your SQL or not. It may be as simple as your <% response.write rs1("total") %> is not in the while not...wend loop.
    To say my fate is not tied to your fate is like saying, 'Your end of the boat is sinking.' -- Hugh Downs
    Please, if you found my post helpful, pay it forward. Go and help someone else today.

  • #7
    Regular Coder
    Join Date
    Jul 2005
    Location
    Oxfordshire, UK
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts
    nope, the totals are no where near in order!

    this is the query: rs1.open "SELECT studentid, COUNT(*) as [total] FROM blue_form group by studentid ORDER BY 'total' DESC", DB

  • #8
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    In Access, can you run the same query and see what you get?

    If it won't run correctly, use Access' Query Wizard (or drag 'n' drop GUI) to build the query. Then, do a VIEW > SQL, and copy/paste from Access to your ASP.

    It should work flawlessly from there.

    Access has issues with aliases that are not exactly formed as it wants them.
    To say my fate is not tied to your fate is like saying, 'Your end of the boat is sinking.' -- Hugh Downs
    Please, if you found my post helpful, pay it forward. Go and help someone else today.

  • #9
    Regular Coder
    Join Date
    Jul 2005
    Location
    Oxfordshire, UK
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts
    this is what access gave me and it works!

    Code:
    SELECT blue_form.studentid, Count(*) AS total FROM blue_form GROUP BY blue_form.studentid ORDER BY Count(*) DESC

    can you suggest how i limit it to 20 records, tried putting limit 20 on the end of DESC but it moans, also tried SELECT TOP 20 but oddly it gives me 23 records...

  • #10
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    Well, adding the LIMIT syntax on the end is correct. Why it's moaning, I can't tell you.

    Try the same Access trick again and add the LIMIT. See what it gives you.

    Side Logic: If SELECT TOP 20 = 23 records, why not try SELECT TOP 17?
    To say my fate is not tied to your fate is like saying, 'Your end of the boat is sinking.' -- Hugh Downs
    Please, if you found my post helpful, pay it forward. Go and help someone else today.


  •  

    Posting Permissions

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