Conditional Sum in Group by Query Mssql

Conditional sum in Group By query MSSQL

Try

SELECT OrderId, 
       COUNT(*) ItemCount,
       SUM(CASE WHEN PriceType = 'CopyCost' THEN Price ELSE 0 END) TotalCopyCost,
       SUM(CASE WHEN PriceType = 'FullPrice' THEN Price ELSE 0 END) TotalFullPrice
  FROM OrderDetails
 GROUP BY OrderId

SQLFiddle

How to use GROUP BY with MAX or SUM depending on conditions?

You can use MAX() and SUM() window functions:

SELECT DISTINCT Name,
       CASE 
         WHEN Name IN ('name1', 'name2') THEN
           MAX(CASE WHEN Date <= '2021-07-01' THEN Value END) OVER (PARTITION BY Name)
         WHEN Name IN ('name3', 'name4') THEN
           SUM(Value) OVER (PARTITION BY Name)
       END Value    
FROM tablename

Or, conditional aggregation:

SELECT Name,
       CASE 
         WHEN Name IN ('name1', 'name2') THEN MAX(CASE WHEN Date <= '2021-07-01' THEN Value END)
         WHEN Name IN ('name3', 'name4') THEN SUM(Value)
       END Value    
FROM tablename
GROUP BY Name

See the demo.

getting a conditional sum with group by

I would recommend that you calculate BLA taking that condition in consideration. But, another way is just using your current query as a derived table and use a CASE:

SELECT  SUM(Jan) Jan,
        SUM(Feb) Feb,
        SUM(Mar) Mar,
        SUM(Apr) Apr,
        SUM(May) May,
        SUM(Jun) Jun,
        SUM(Jul) Jul,
        SUM(Aug) Aug,
        SUM(Sep) Sep,
        SUM(Oct) Oct,
        SUM(Nov) Nov,
        SUM(Dec) Dec,
        CASE WHEN Bla >= 41 THEN 41 ELSE Bla END Bla
FROM (Your current query here) DT
GROUP BY CASE WHEN Bla >= 41 THEN 41 ELSE Bla END;

Or a CTE:

;WITH CTE AS 
(
    Your current query here
)
SELECT  SUM(Jan) Jan,
        SUM(Feb) Feb,
        SUM(Mar) Mar,
        SUM(Apr) Apr,
        SUM(May) May,
        SUM(Jun) Jun,
        SUM(Jul) Jul,
        SUM(Aug) Aug,
        SUM(Sep) Sep,
        SUM(Oct) Oct,
        SUM(Nov) Nov,
        SUM(Dec) Dec,
        CASE WHEN Bla >= 41 THEN 41 ELSE Bla END Bla
FROM CTE
GROUP BY CASE WHEN Bla >= 41 THEN 41 ELSE Bla END;

SQL GROUP BY and COUNT and conditional SUM with column value

use this

SELECT USERID,
SUM(CASE WHEN STATUS = 1 THEN VALUE ELSE 0 END ) AS ST1,
SUM(CASE WHEN STATUS = 0 THEN VALUE ELSE 0 END ) AS ST2 FROM 
DBO.TABLENAME
GROUP BY USERID

conditional sum of one column based on another and then all grouped by a third

I'm not sure why you need a sub-query, if you don't have a pattern for bands, maybe try this way

SELECT  A,
    SUM(CASE WHEN C <= 5 THEN B ELSE 0 END) as band_1,
    SUM(CASE WHEN C > 5 and C <= 10 THEN B ELSE 0 END) as band_2
FROM MyTable
GROUP BY A  

SQL - Conditional Group By Statement With Sum

You can use aggregation, and filter out branches that have at least one voluntary with a having clause:

select branch_id, sum(earnings) as earnings
from earnings_table
group by branch_id
having max(case when employee_type = 'Voluntary' then 1 else 0 end) = 0

Some databases have neater options to express the having clause.

In MySQL or SQLite:

having max(employee_type = 'Voluntary') = 0

In Postgres;

having not bool_or(employee_type = 'Voluntary')

sql for Group By and SUM with date range condition

I suggest just making a single pass over the invoices table using conditional aggregation for the various time windows:

SELECT
    company_id,
    SUM(CASE WHEN due_date >= '2021-11-10' THEN balance_amount ELSE 0 END) AS cmonth,
    SUM(CASE WHEN due_date >= '2021-10-10' AND due_date < '2021-11-10'
             THEN balance_amount ELSE 0 END) AS 1month,
    SUM(CASE WHEN due_date >= '2021-09-10' AND due_date < '2021-10-10'
             THEN balance_amount ELSE 0 END) AS 2month
FROM invoices
WHERE
    status = 'ACTIVE' AND balance_amount > 0 AND deleted_at IS NULL
GROUP BY
    company_id;

SQL SUM group with condition

You confused statuses and totals in your CASE expression. Moreover, you are making it more complicated than necessary. You want to add up a total when its status is 0:

SELECT
  vendor, 
  SUM(CASE WHEN status_inv = 0 THEN total_inv ELSE 0 END) +
      CASE WHEN status_1   = 0 THEN total_1   ELSE 0 END) +
      CASE WHEN status_2   = 0 THEN total_2   ELSE 0 END)) AS grand_total
FROM tbInvoice
GROUP BY vendor  
ORDER BY vendor;

Without any math tricks ;-)

SQL Server - SUM() and Group() Records based on a condition

My solution uses a subquery that determines the last activity date for each player

SELECT
    PlayerId,
    MAX(ActivityDate) AS LastDate
FROM
    dbo.PlayerActivity
WHERE PlayerActivityTyepId = 14
GROUP BY PlayerId

The main query uses a GROUP BY as well to aggregate the results per team and year/month. The trick is done in a CASE expression. Here I compare the actual activity date with the last one determined by the subquery. The points get only attributed on the last activity and otherwise 0.

SELECT
    TP.TeamId,
    SUM(
        CASE
           WHEN PA.ActivityDate = X.LastDate
           THEN P.TotalMatchesPlayed * 5
           ELSE 0
        END
    ) AS ParticipationPoints,
    RIGHT(CAST(100+MONTH(PA.ActivityDate) AS VARCHAR(3)), 2) AS [Month],
    CAST(YEAR(PA.ActivityDate) AS VARCHAR(4)) AS [Year]
FROM
    dbo.TeamPlayer TP
    INNER JOIN dbo.Player P
        ON TP.PlayerId = P.PlayerId
    INNER JOIN dbo.PlayerActivity PA
        ON PA.PlayerId = P.PlayerId AND PA.PlayerActivityTyepId = 14
    INNER JOIN (
        SELECT
            PlayerId,
            MAX(ActivityDate) AS LastDate
        FROM
            dbo.PlayerActivity
        WHERE PlayerActivityTyepId = 14
        GROUP BY PlayerId
    ) AS X
        ON X.PlayerId = P.PlayerId
WHERE TP.TeamId = 45
GROUP BY
    TP.TeamId, YEAR(PA.ActivityDate), MONTH(PA.ActivityDate)

http://sqlfiddle.com/#!18/41766/28/0

Note that I am assuming that there is only one activity per day per player. If there are more, the points will be summed up repeatedly. If the PlayerActivityId values are strictly inserted in ascending order, you can fix this by working the with MAX(PlayerActivityId) instead of MAX(ActivityDate).

To get the month with two digits I first calculate month+100 and convert this to VARCHAR(3). This yields '104' for April. Then I get the result by returning the last two characters with the RIGHT function. Of course, you could use proper formatting functions, but I was to lazy to look up the right functions and formats to use.

---

Related Topics