CODEWITHSUNDEEP
sql-servert-sqlcount
SP007
SP007

Reputation: 1921

Need to get count based condition in select clause based on group by Date in tsql

DECLARE @testTable TABLE (OrderDate date, StoreType int, Fruits int, Vegetables int, eggs int)

INSERT INTO @testTable VALUES(getDate(), 1, 4, 6, 9); 
INSERT INTO @testTable VALUES(getDate(), 1, 5, 7, 3); 
INSERT INTO @testTable VALUES(getDate(), 2, 8, 2, 1); 
INSERT INTO @testTable VALUES(getDate()-1, 1, 7, 2, 8); 
INSERT INTO @testTable VALUES(getDate()-1, 1, 0, 9, 4); 
INSERT INTO @testTable VALUES(getDate()-1, 2, 5, 6, 1); 
INSERT INTO @testTable VALUES(getDate()-2, 1, 7, 2, 5); 
INSERT INTO @testTable VALUES(getDate()-2, 2, 6, 6, 6); 
INSERT INTO @testTable VALUES(getDate()-2, 1, 5, 6, 3); 
INSERT INTO @testTable VALUES(getDate()-3, 1, 9, 3, 3); 
INSERT INTO @testTable VALUES(getDate()-3, 2, 6, 0, 1); 
INSERT INTO @testTable VALUES(getDate()-3, 1, 2, 7, 4); 

-- NEED TO MODIFY QUERY
SELECT  
    YEAR(OrderDate) AS [YEAR]
    ,MONTH(OrderDate) AS [MONTH]
    ,DAY(OrderDate)  AS [DAY]
    ,CASE WHEN StoreType IN (1,2) THEN SUM(Fruits) END AS FruitCount
    ,CASE WHEN StoreType IN (1,2) THEN SUM(Vegitables) END AS VegetablesCount
    ,CASE WHEN StoreType IN (1,2) THEN SUM(eggs) END  AS EggsCount
    ,StoreType
    ,(CASE WHEN StoreType = 1 THEN SUM(Fruits + vegitables + eggs) END) AS [CountBY Stype(1)]
FROM 
    @testTable
GROUP BY 
    YEAR(OrderDate),
    MONTH(OrderDate),
    DAY(OrderDate),
    StoreType

Current result set:

   YEAR MONTH   DAY FruitCount  VegitalbesCount EggsCount   StoreType   CountBySType(1)
2015     2       9     11            10            7            1          28
2015     2       9     6             0             1            2         NULL
2015     2       10    12            8              8           1           28
2015     2      10      6            6              6           2         NULL
2015     2      11      7           11              12          1           30
2015    2       11      5           6               1           2           NULL
2015    2       12      9           13              12          1           34
2015    2       12      8           2               1           2           NULL

Expected / desired output:

        YEAR    MONTH   DAY FruitCount  VegitalbesCount EggsCount   CountBY Stype(1)
        2015     2       9     17               10          8            28
        2015     2      10     18               14         14            28
        2015     2      11     12               17         13            30
        2015     2      12     17               15         13            34

Is there any way can I achieve result set like above(EXPECTED)? We can use sub query, but due to high volume of data query makes so slow.

Upvotes: 1

Views: 300

Answers (2)

SP007
SP007

Reputation: 1921

I change query below to achieve. thanks for everyone for reading and your solutions.

SELECT  YEAR(OrderDate) AS [YEAR]
        ,MONTH(OrderDate) AS [MONTH]
        ,DAY(OrderDate)  AS [DAY]
        ,SUM(COALESCE(Fruits,0)) AS FruitCount
        ,SUM(COALESCE(Vegitables,0)) AS VegitalbesCount
        ,SUM(COALESCE(eggs,0)) AS EggsCount,
        SUM(CASE WHEN StoreType IN (1) THEN COALESCE(Fruits,0)+ COALESCE(Vegitables,0)+ COALESCE(eggs,0) END) [Total]
FROM @testTable
GROUP BY YEAR(OrderDate),MONTH(OrderDate),DAY(OrderDate)

Upvotes: 1

Neel
Neel

Reputation: 202

You need to take out the StoreType from GroupBy

Try this:

SELECT  
YEAR(OrderDate) AS [YEAR]
,MONTH(OrderDate) AS [MONTH]
,DAY(OrderDate)  AS [DAY]
,SUM(CASE WHEN StoreType IN (1,2) THEN Fruits END )AS FruitCount
,SUM(CASE WHEN StoreType IN (1,2) THEN Vegetables END )AS VegetablesCount
,SUM(CASE WHEN StoreType IN (1,2) THEN eggs END)  AS EggsCount
,SUM(CASE WHEN StoreType = 1 THEN Fruits + Vegetables + eggs END) 
FROM @testTable
GROUP BY 
  YEAR(OrderDate),
  MONTH(OrderDate),
  DAY(OrderDate)

Upvotes: 1

Related Questions