CODEWITHSUNDEEP
sqlsql-server
Tauseef Hussain
Tauseef Hussain

Reputation: 1079

SQL: Get the first value of a GroupBY Clause

USE AQOA_Core

SELECT TOP 10 
    p.Title, SUM(r.SalesVolume) AS Sales, c.WeekId AS ReleaseWeek
FROM 
    Product p
INNER JOIN 
    RawData r ON p.ProductId = r.ProductId
INNER JOIN 
    Calendar c ON r.DayId = c.DayId
WHERE 
    c.WeekId BETWEEN 20145227 AND 20145228
GROUP BY 
    p.Title, c.WeekId
ORDER BY 
    Sales DESC, ReleaseWeek DESC

The result set of the above query is:

Title   Sales   ReleaseWeek
Movie1  10      20145228
Movie1  10      20145227
Movie2  10      20145228
Movie2  10      20145227
Movie3  10      20145228
Movie3  10      20145227
Movie4  10      20145228
Movie4  10      20145227
Movie5  10      20145228
Movie5  10      20145227

I basically want only the first week from the ReleaseWeek column but still groupby Title column

The ideal result set would look like:

Title   Sales   ReleaseWeek
Movie1  20      20145228
Movie2  20      20145228
Movie3  20      20145228
Movie4  20      20145228
Movie5  20      20145228

How should I go about doing this? Probably by a subquery?

The dataset used here is just for simple representation. The data in the actual dataset is much larger.

Upvotes: 1

Views: 216

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You just want the minimum of the second column, so you can use MIN():

SELECT TOP 10 p.Title, SUM(r.SalesVolume) AS Sales,
       MIN(c.WeekId) AS ReleaseWeek
FROM Product p INNER JOIN 
     RawData r
     ON p.ProductId = r.ProductId INNER JOIN 
     Calendar c
     ON r.DayId = c.DayId
WHERE c.WeekId BETWEEN 20145227 AND 20145228
GROUP BY p.Title
ORDER BY Sales DESC, ReleaseWeek DESC;

Note that this requires removing c.weekid from the GROUP BY as well as adding the MIN() to the SELECT.

Upvotes: 1

t-clausen.dk
t-clausen.dk

Reputation: 44326

You need to select the first 10 rows in a sub select. Then from the sub select, use an aggregate. This will choose the latest ReleaseWeek like your test data instead of the first ReleaseWeek as your text describes. You can change this to MIN, if that was what you meant:

;WITH CTE as
(
SELECT TOP 10 
    p.Title, r.SalesVolume, c.WeekId
FROM 
    Product p
INNER JOIN 
    RawData r ON p.ProductId = r.ProductId
INNER JOIN 
    Calendar c ON r.DayId = c.DayId
WHERE 
    c.WeekId BETWEEN 20145227 AND 20145228
ORDER BY 
    Sales DESC, ReleaseWeek DESC
)
SELECT 
  Title, SUM(SalesVolume) AS Sales, MAX(WeekId) ReleaseWeek
FROM CTE
GROUP BY Title

Since you want to aggrigate WeekId, you can't include it in your GROUP BY

Upvotes: 1

user359135
user359135

Reputation:

Have you tried:

SELECT TOP 10 
    p.Title, SUM(r.SalesVolume) AS Sales, Min(c.WeekId) AS ReleaseWeek
FROM 
    Product p
INNER JOIN 
    RawData r ON p.ProductId = r.ProductId
INNER JOIN 
    Calendar c ON r.DayId = c.DayId
WHERE 
    c.WeekId BETWEEN 20145227 AND 20145228
GROUP BY 
    p.Title
ORDER BY 
    Sales DESC, ReleaseWeek DESC

Your week ids are alphabetical or numerical so as far as i am aware you will get the first week with a min.

I have not used this technique in a while and i am pretty sure it does not work in all DBs

Also as commenter mentioned you may need Max instead of Min as looking at your example you are taking the last date not the first.

Upvotes: 2

Related Questions