SUMing based on two columns in SQL

user_19153674 0
Viewed 74

First post so sorry! I have:

PARTICIPANT VALUE RULE PERIOD
JAMES 0 RULE 1 2022-12-1
JAMES 0 RULE 1 2022-01-1
JAMES 1 RULE 1 2022-02-1
JAMES 1 RULE 2 2022-12-1
JAMES 1 RULE 2 2022-01-1
JAMES 1 RULE 2 2022-02-1

I want:

PARTICIPANT VALUE
JAMES 1

I tried:

SELECT [PARTICIPANT], SUM([VALUE]), [Period]
FROM (
SELECT [PARTICIPANT], [VALUE], [RULE], [Period]
FROM [TABLE]
WHERE [RULE]='RULE 1'
UNION
SELECT [PARTICIPANT], [VALUE], [RULE], [Period]
FROM [TABLE]
WHERE [RULE]='RULE 2'
) AS [T1]
GROUP BY [Participant], [Period]

I have four columns [Participant, Value, Rule and Period] There are only two types of [Rule].

I want to Select the participant's value for each Period only if the value of Rule 1 is >= to value of Rule 2.

I am not sure how to denote the comparison since each participant has multiple periods and each period may or may not have both rules.

I also want to sum the value of the selected periods by participant to perform functions on later.

sql-server sql
2 Answers

The following should do you good


SELECT [TR1].[PARTICIPANT], SUM([TR1].[VALUE]) AS [VALUE], [TR1].[Period]
FROM 
(
    SELECT [PARTICIPANT], SUM([VALUE]) AS [VALUE], [RULE], [Period]
    FROM [TABLE]
    WHERE [RULE]='RULE 1'
    GROUP BY [PARTICIPANT], [RULE], [Period]
) [TR1] LEFT JOIN
(
    SELECT [PARTICIPANT], SUM([VALUE]) AS [VALUE], [RULE], [Period]
    FROM [TABLE]
    WHERE [RULE]='RULE 2'
    GROUP BY [PARTICIPANT], [RULE], [Period]
) [TR2]
ON [TR1].[PARTICIPANT] = [TR2].[PARTICIPANT] AND 
    [TR1].[Period] = [TR2].[Period]
WHERE [TR1].[VALUE] >= COALESCE([TR2].[VALUE], 0)
GROUP BY [TR1].[PARTICIPANT], [TR1].[Period]
user_31142350

It looks like you want conditional aggregation per PERIOD, then group again only by PARTICIPANT

SELECT
  t.PARTICIPANT,
  COUNT(CASE WHEN t.Rule1 >= t.Rule2 THEN 1 END) Rule1MoreThan
FROM (
    SELECT
      t.PARTICIPANT,
      t.PERIOD,
      SUM(CASE WHEN t.[RULE] = 'RULE 1' THEN t.VALUE END) Rule1,
      SUM(CASE WHEN t.[RULE] = 'RULE 2' THEN t.VALUE END) Rule2
    FROM [TABLE] t
    GROUP BY
      t.PARTICIPANT,
      t.PERIOD
) t
GROUP BY
  t.PARTICIPANT;

db<>fiddle

user_148689970
Related