What would be the correct SQL Server statement for this scenario?-CodePudding

I have created a Visual Studio application that tracks the amount of time someone spends on some another desktop application (a productivity app, if you may). This application has a backend of an SQL server (MSSQL specifically, considering the dialect). In a page of this application, I am attempting to select and display some already input data through an SQL query. I want the query to carry out the following:

The accessed desktop apps that have the same name are grouped together (1) and the amount of time that is spent on these is added (2). Then, these are ordered in an descending order (3), and the top 5 records are selected (4).

E.g.

CREATE TABLE ApplicationInfo {
     AppName varchar(50)
     Duration int
}

INSERT INTO ApplicationInfo (AppName, Duration)
    Values
    (Word, 1), (Spotify, 2)
    (Spotify, 1), (Word, 2), 
    (Discord, 2), (Access, 4),
    (Notepad, 3)

As a proper table, that is:

| AppName | Duration |
|---------|----------|
|  Word   |    1     |
| Spotify |    2     |
| Spotify |    1     |
|  Word   |    2     |
| Discord |    2     |
| Access  |    4     |
| Notepad |    3     |

(I have tried using table markdown, but the table format does not render, for some reason)

After steps (1) and (2),

| AppName | Duration |
|---------|----------|
|  Word   |    3     |
| Spotify |    3     |
| Discord |    2     |
| Access  |    4     |
| Notepad |    3     |

After steps (3) and (4),

| AppName | Duration |
|---------|----------|
| Access  |    4     |
| Notepad |    3     |
| Spotify |    3     |
|  Word   |    3     |
| Discord |    2     |

I have tried this SQL statement with test data, but it does not return what I want it to return:

SELECT TOP 5 AppName, DurationHrs, SUM(DurationHrs) OVER (PARTITION BY AppName) FROM ApplicationInfo ORDER BY SUM(DurationHrs) DESC;

I believe the LIMIT keyword would not be suitable for this, due to the MSSQL dialect (as opposed to MySQL). Could someone suggest an SQL statement that would be appropriate for this scenario (following the MSSQL dialect)?

CodePudding user response：

SELECT TOP 5 AppName, SUM(DurationHrs)
FROM ApplicationInfo
GROUP BY AppName
ORDER BY SUM(DurationHrs) DESC

CodePudding user response：

You're close. Ordering by SUM(DurationHrs) without the OVER (PARTITION BY AppName) requires you to do some grouping for the aggregate. If you just want to order by that third column, you could copy the whole expression or give it an alias and use that, or use the order by numeric shortcut:

SELECT TOP 5 AppName, DurationHrs, SUM(DurationHrs) OVER (PARTITION BY AppName) FROM ApplicationInfo ORDER BY SUM(DurationHrs) OVER (PARTITION BY AppName)  DESC;
SELECT TOP 5 AppName, DurationHrs, SUM(DurationHrs) OVER (PARTITION BY AppName) total_durationHrs FROM ApplicationInfo ORDER BY total_durationHrs   DESC;
SELECT TOP 5 AppName, DurationHrs, SUM(DurationHrs) OVER (PARTITION BY AppName) FROM ApplicationInfo ORDER BY 3 DESC;

If you want to group the data by AppName then you would do:


SELECT TOP 5 AppName, SUM(DurationHrs) 
FROM   ApplicationInfo
GROUP BY AppName
ORDER BY SUM(DurationHrs)   DESC;