CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user1773949
user1773949

Reputation: 119

Using derived table to group by ALIAS

I need help rewriting the following query as I need to group by the aliases and derived tables appear to be the only path open to me. I cannot group by Type and SalesDatabase as I get an invalid column error.

SELECT    'Transactions'        AS Type,  
          'SalesDb'             AS SalesDatabase,  
          COUNT (B.Id)          AS Total,  
          CASE 
              WHEN SB.name LIKE '%Online%'      THEN 'Online'   
              WHEN SB.name LIKE '%RetailSale%'  THEN 'Retail'   
              ELSE 'Bricks'   
          END AS SalesType,    
          ISNULL( SUM( CASE WHEN datediff( d, b.Inserted, getDate() ) <  1 THEN 1 ELSE 0 END ), 0 ) AS SalesPeriod0,          
          ISNULL( SUM( CASE WHEN datediff( d, b.Inserted, getDate() ) >=  1 and datediff( d, b.Inserted, getDate() ) < 8 THEN 1 ELSE 0 END ), 0 ) AS SalesPeriod1to7 ,         
          ISNULL( SUM( CASE WHEN datediff( d, b.Inserted, getDate() ) >= 8 THEN 1 ELSE 0 END ), 0 ) AS SalesPeriod8p  
FROM SalesItem B WITH (NOLOCK)  
INNER JOIN SalesBatch SB WITH (NOLOCK) ON SB.Id  = B.BatchId    
GROUP BY SB.NAME

Any help appreciated.

Upvotes: 1

Views: 491

Answers (2)

RichardTheKiwi
RichardTheKiwi

Reputation: 107766

The query as you have written is correct, if you intend to produce one row PER SB.NAME. It means that you could have

SB.NAME    | SalesType   | ...
ABC        | Online      | ...
DEF        | Online      | ...
XYZ        | Retail      | ...
DEF1       | Retail      | ...

However, if you want to group by SalesType, then use this GROUP BY

GROUP BY CASE 
          WHEN SB.name LIKE '%Online%'      THEN 'Online'   
          WHEN SB.name LIKE '%RetailSale%'  THEN 'Retail'   
          ELSE 'Bricks'   
      END

Upvotes: 1

bummi
bummi

Reputation: 27385

There is no need for grouping the constants Type and SalesDatabase.

GROUP BY SB.NAME,  
          CASE 
              WHEN SB.name LIKE '%Online%'      THEN 'Online'   
              WHEN SB.name LIKE '%RetailSale%'  THEN 'Retail'   
              ELSE 'Bricks'   
          END

Upvotes: 0

Related Questions