CODEWITHSUNDEEP
sqlsql-servert-sql
Pankash Mann
Pankash Mann

Reputation: 59

Case in Sql group by query

I am working on a project in which I want to use Case to calculate price of product under specific Reference Number in SQL server. Below is my Sql query 

SELECT
    product AS Products,
    refNum AS Refrence,
    COUNT(id) AS Count 
FROM ProductPriceList 
GROUP BY
    refNum, product

By Executing Above query I get:

Product      Reference   Count
Product1     Ref08       24
Product2     Ref08       7
Product3     Ref07       32
Product2     Ref12       1
Product3     Ref12       18
Product1     Ref07       76  
Product1     Null        56

Can anyone guide me how to use Case statement in Sql query with group by statement to show price Below is the case:

if count < 10 then price 1
if count > 10 and < 100 then price 2
if count > 100 then price 3

I don't want to add a new table in my database. I hope you can understand my query.

Thanks in advance.

Upvotes: 1

Views: 283

Answers (3)

Tolu
Tolu

Reputation: 175

Dataset:

Create Table ProductPriceList 
(
 Product varchar(10)
,RefNum CHAR(5)
,Records Int
);

Insert into ProductPriceList
Values
 ('Product1','Ref08',24)
,('Product2','Ref08',7)
,('Product3','Ref07',32)
,('Product2','Ref12',1)
,('Product3','Ref12',18)
,('Product1','Ref07',76)
,('Product1', NULL, 56);

With RCTE AS
(
Select   Product
        ,RefNum
        ,Records
        ,1 RowNo
From ProductPriceList PPL
Union All
Select   Product
        ,RefNum
        ,Records
        ,RowNo + 1
From RCTE R
Where RowNo + 1 < Records
)
Insert Into ProductPriceList (Product, RefNum, Records)
Select Product, RefNum, Records
From RCTE
where Records > 1

Query to fetch desired result:

Select   Product
        ,RefNum 
        ,Case When Count(*) < 10 Then 1
              When Count(*)  Between 10 and 99 then 2
        Else 3 End Price
From ProductPriceList
Group By Product, RefNum

SQL Fiddle

Upvotes: 1

Michał Turczyn
Michał Turczyn

Reputation: 37347

Here's alternative (doesn't differ much from exisiting one though):

You can use your query in subquery and use case outside:

select product,
       --to get NULL values back
       case Reference when 'RefNull' then NULL else Reference end [Reference],
       case when [Count] < 10 then 1
            when [Count] between 10 and 100 then 2
            else 3 end [price]
from (
     SELECT product AS Products,
            --to allow also null values to be grouped
            coalesce(refNum, 'RefNull') AS Refrence,
            COUNT(id) AS Count 
    FROM ProductPriceList 
    GROUP BY coalesce(refNum, 'RefNull'), product
) [a]

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520958

I think a basic CASE expression can handle your requirement:

SELECT
    product AS Products,
    refNum AS Refrence,
    CASE WHEN COUNT(*) < 10 THEN 1
         WHEN COUNT(*) >= 10 AND COUNT(*) < 100 THEN 2
         ELSE 3 END AS price
FROM ProductPriceList 
GROUP BY
    product, refNum;

Not much to explain here, except that the 2 price case uses a bound which includes the count of 10 (since the 1 price case excludes it).

Upvotes: 4

Related Questions