A (slightly complex) SQL query?
I have this tricky (for me) requirement and would appreciate an SQL query for it. Here's the deal:
- This table shows the table "Costs" in the access file.
- The table contains the fields "Category", "Amount", "Payer" and "Outcome".
- The category can be gas, grocery, home, etc.
- Amount is the dollar amount due.
- The payer can be me or my brother ("I" or "Bro").
- IsShare can be true or false. Basically, if IsShare is true, then the rate is halved. Otherwise, the payer pays for all this.
I need a SQL query to calculate all my expenses for a specific category. It should be calculated as such:
- 100% of all non-shareable items I paid (Payer = 'Me' AND IsShare = false)
- 50% of all shared items I paid (Payer = 'Me' AND IsShare = true) and
- 50% of all shared items my brother paid (Payer = 'Bro' AND IsShare = true).
The request should return the sum of all three of these items.
So far, I have a preliminary query, but it is returning incorrect results. Can someone fix this for me?
Many thanks.
return @"SELECT Sum(Amount) AS TotalAmount " +
"FROM Expenses " +
"WHERE Category = 'Groceries' " +
"AND Payer = 'Me' " +
"AND Share = false " +
"GROUP BY Category " +
"UNION " +
"SELECT 0.5 * Sum(Amount) AS TotalAmount " +
"FROM Expenses " +
"WHERE Category = 'Groceries' " +
"AND Payer = 'Me' " +
"AND Share = true " +
"GROUP BY Category " +
"UNION " +
"SELECT 0.5 * Sum(Amount) AS TotalAmount " +
"FROM Expenses " +
"WHERE Category = 'Groceries' " +
"AND Payer = 'Bro' " +
"AND Share = true ";
"GROUP BY Category";
+3
source to share
1 answer
I don't think UNION is necessary for what you are trying to do. This will pass the data once and put the values in separate columns. You may need to cast or convert 0.5 to a specific type, I haven't had a chance to run that.
SELECT
Category,
SUM(CASE WHEN Payer='me' AND NOT IsShare THEN Amount ELSE 0 END) as IPaid,
SUM(CASE WHEN Payer='me' AND IsShare THEN Amount * 0.5 ELSE 0 END) as SharedPay,
SUM(CASE WHEN Payer='bro' AND IsShare THEN Amount * 0.5 ELSE 0 END) as BrotherPay
FROM Eexpenses
WHERE Category = 'Groceries'
GROUP BY Category
For MS ACCESS:
SELECT
Category,
SUM(IIF(Payer='me' AND NOT IsShare, Amount,0)) as IPaid,
SUM(IIF(Payer='me' AND IsShare, Amount * 0.5, 0)) as SharedPay,
SUM(IIF(Payer='bro' AND IsShare, Amount * 0.5, 0)) as BrotherPay
FROM Eexpenses
WHERE Category = 'Groceries'
GROUP BY Category
Add three columns together:
SELECT Category, IPaid, SharedPay, BrotherPay, IPay + SharedPay + BotherPay as Total
FROM (
SELECT
Category,
SUM(IIF(Payer='me' AND NOT IsShare, Amount,0)) as IPaid,
SUM(IIF(Payer='me' AND IsShare, Amount * 0.5, 0)) as SharedPay,
SUM(IIF(Payer='bro' AND IsShare, Amount * 0.5, 0)) as BrotherPay
FROM Eexpenses
WHERE Category = 'Groceries'
GROUP BY Category
) as T1
+2
source to share