Hi there first time poster, I’m learning some SQL on my own and I setup a small db for my finances. I have 2 tables, expenses table with the name of a bill the and the total amount per month, and my second table, expenses_date that has the date of the bill and a FK pointing back to the expenses table. There are some bill which occur multiple times per month so they have multiple dates associated with a bill. I’m trying to write a query which will COUNT( ) the number of occurrences of the bill then divide it with the total amount. My issue is I can get the COUNT( ) and divide by the total amount, but I have to use a group by argument at the end of the query so I can’t view all the dates,
SELECT Expenses_Id, e.Name, e.Amount, COUNT(ed.Expenses_Id) AS 'Amount per Iteration' FROM Expense_Dates ed JOIN Expenses e ON ed.Expenses_Id = e.expense_id GROUP BY ed.Expenses_Id, e.Name, e.Amount
|Expenses_Id| Name| Amount| Amount per Iteration| |1| Rent| 612.00| 1| |2| Spotify| 9.99| 1| |3| Groceries| 300.00| 4| |4| Adobe| 14.42 1| |5| Disney+| 8.99| 1| |6| Utilities| 149.43| 1| |7| Phone Bill| 109.26| 1|
So basically I want groceries to be divided by 4, but I want it to be divided by 4 for each iteration of it and not grouped together.