Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Feb 2010
    Posts
    12

    Red face Unanswered: NEED HELP! with sql query

    Hi Everyone,
    I am working on a database and need to work out a query that would display the most used applications in my company per month. These need to be ranked by the top 10 most used applications.

    I am not very good with sql as it is a new language to me and need your help.

    Here are the details:

    Entities:
    ApplicationTbl:
    AppID as an AutoNumber(PK)
    AppName as a Text
    SessionTbl:
    SessionID as an AutoNumber(PK)
    SessionStart as a Date/Time
    SessionEnd as a Date/Time
    TotalTimeSum as a number
    ActiveTimeSum as a number
    AppID as number (FK)

    From these two tables I have worked out how to count the usage of each application, but don't know how to rank them.

    Also any application name after the 10th row needs to be named as 'other'

    Below is the code that I have so far:

    SELECT ApplicationTbl.AppName, Count(*) AS SessionNos
    FROM ApplicationTbl INNER JOIN SessionTbl ON ApplicationTbl.AppID = SessionTbl.AppID
    GROUP BY ApplicationTbl.AppName;

    Any help is appreciated.

    Thanks!

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Look up the ORDER BY clause (to order by the count descending) and also the T-SQL TOP expression.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Is this Access or SQL Server? I have locked the SQL Server version for now.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Feb 2010
    Posts
    12
    Its an SQL query in Access 2007

  5. #5
    Join Date
    Feb 2010
    Posts
    12
    I have worked out the values, but just need to select the top 10, also anything other than the top 10 applications needs to be classified as one and added to the 11th row as 'other'

  6. #6
    Join Date
    Feb 2010
    Posts
    12
    SELECT ApplicationTbl.AppName, Count(*) AS SessionNos
    FROM ApplicationTbl INNER JOIN SessionTbl ON ApplicationTbl.AppID = SessionTbl.AppID
    GROUP BY ApplicationTbl.AppName;
    ORDER BY Count(*) DESC;

    I have ordered all the applications by their usage in order, now I need to list the top 10 and everything else added to the 11th row and named 'other'

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You need more than the top ten then. You need all of them. The complication here is for 11 plus to be marked "other". If you could get rid of this requirement then it is trivial (see my post #2). If you cannot then it is more complicated when using Access SQL. Other SQL dialects make this a reasonably easy task.

    Please could you also tell me what you want to have happen in the event of a tie for 10th place?
    EDIT - you posted before I had finished typing.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Feb 2010
    Posts
    12
    The 11 plus row is compulsary, allthough its very unlikely that there will be a duplicate value due to the size of the application usage numbers, however if it does occur then the first selected application could be chosen.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok.

    For now can you incorporated the TOP 10 expression into your query and repost please? We fill first of all get the 10 applications and then we will then build on that.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Feb 2010
    Posts
    12
    Sorry my SQL knowledge is quite poor...I tried RANK and DENSE RANK and also ROW_NUMBER, but no success init...would you be able to help with this pls?

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - let's start again.
    Is this SQL Server? Or is this Access?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Just to be clear, SQL is a language NOT a database product.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Feb 2010
    Posts
    12
    yepp thats right, its in MS Access 2007 and the query is in SQL

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I want to be completely certain here: when you say SQL you do not mean SQL Server. Correct?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Feb 2010
    Posts
    12
    I am using MS Access and SQL qeury function that comes with MS Access. Instead of creating the queries using the query design view in Access I am using SQL View as I think you have more options.

Tags for this Thread

Posting Permissions

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