CODEWITHSUNDEEP
sqlsql-serversql-server-2012group-byalias
User987
User987

Reputation: 3823

MSSQL query issue - putting alias name in GROUP BY part of the query

I have a query which basically looks like this:

SELECT 
    COUNT(*) OVER () AS TotalRowsFound,
        t.Title
        , t.ItemID
        , t.UploadDate
        , t.SevenDaySales as SelectedColumnSales
        , t.QuantitySold
        , t.CurrentPrice
        , t.CurrentPrice as LastSalePrice

FROM 
        dbo.SearchedUserItems t
WHERE 
        T.SearchedUserID = 5   
GROUP BY 
        t.ItemID,t.UploadDate,t.SelectedColumnSales,t.QuantitySold,t.CurrentPrice,LastSalePrice
ORDER BY SelectedColumnSales  DESC
OFFSET + 55*0 ROWS FETCH NEXT 55 ROWS ONLY;

The issue is that I can't finish the query because it says that columns SelectedColumnSales and LastSalePrice are nonexistent...

Is there any workaround for this ? =/

How can I finish this query for it to actually work and use the aliases in group by statement, or some other way?

Can someone help me out ?

Upvotes: 0

Views: 73

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

I don't understand why you are using a window function like that in an aggregation query. One way to do what you want uses apply:

SELECT      . . . 
FROM        dbo.searcheduseritems sui 
CROSS apply (VALUES 
            ( 
                        sevendaysales, 
                        currentprice 
            ) 
            ) v(selectedcolumnsales, lastsaleprice) 
WHERE       sui.searcheduserid = 5 
GROUP BY    sui.itemid, 
            sui.uploaddate, 
            v.selectedcolumnsales, 
            sui.quantitysold, 
            sui.currentprice, 
            v.lastsaleprice 
ORDER BY    v.selectedcolumnsales DESC offset + 55*0 rowsFETCH next 55 rows only

The query doesn't make sense to me (as being something useful), but this will allow you to do what you want.

Upvotes: 1

jarlh
jarlh

Reputation: 44766

Wrap the original query up in a derived table (sub-query), then you can use the column aliases in the GROUP BY:

SELECT
     COUNT(*) OVER () AS TotalRowsFound,
     dt.*
FROM
(
    SELECT 
            t.Title
            , t.ItemID
            , t.UploadDate
            , t.SevenDaySales as SelectedColumnSales
            , t.QuantitySold
            , t.CurrentPrice
            , t.CurrentPrice as LastSalePrice
    FROM 
            dbo.SearchedUserItems t
    WHERE 
            T.SearchedUserID = 5   
) dt
GROUP BY ItemID, UploadDate, SelectedColumnSales, QuantitySold, CurrentPrice, LastSalePrice
ORDER BY SelectedColumnSales  DESC
OFFSET + 55*0 ROWS FETCH NEXT 55 ROWS ONLY;

Upvotes: 0

DimUser
DimUser

Reputation: 270

Due to the way the query is processed by SQL Server, you cannot use aliases in the GROUP BY clause.

SQL Server will process your query starting at the FROM clause. In your case processing will then move to WHERE, and then GROUP BY, before moving to SELECT.

Because of this, by definition your alias SelectedColumnSales does not yet exist.

You need to use actual column names in your group by definition, you cannot use column aliases.

Swap SelectColumnSales for t.SevenDaySales and LastSalePrice for t.CurrentPrice in the GROUP BY

Upvotes: 2

Greg Viers
Greg Viers

Reputation: 3523

It's a simple fix. You can't use alias names in the GROUP BY. Instead, use the source column name:

SELECT 
    COUNT(*) OVER () AS TotalRowsFound,
        t.Title
        , t.ItemID
        , t.UploadDate
        , t.SevenDaySales as SelectedColumnSales
        , t.QuantitySold
        , t.CurrentPrice
        , t.CurrentPrice as LastSalePrice

FROM 
        dbo.SearchedUserItems t
WHERE 
        T.SearchedUserID = 5   
GROUP BY 
        t.ItemID,t.UploadDate,t.SevenDaySales,t.QuantitySold,t.CurrentPrice
ORDER BY SelectedColumnSales  DESC
OFFSET + 55*0 ROWS FETCH NEXT 55 ROWS ONLY;

Upvotes: 1

Related Questions