Help with monthly total for payees in columns query

[omalleypat]

I've sqlite talking with OOO as described in viewtopic.php?f=12&t=610. I'm trying to create a query where I can make a graph of how I'm spending on groceries at different types of stores (i.e. Supermarket, Convenience Store, Wholesale, Organic, Farmer's Market, etc.) each month but I can't get the query to output what I want.

I've gotten this to work:

Code: Select all

SELECT "year", "month", "payee", SUM( "amount" ) FROM "alldata" WHERE ( "category" = 'Food' AND "subcategory" = 'Groceries' ) GROUP BY "year", "month", "payee" ORDER BY "year", "month", "payee"

Which makes a table with columns "Year" "Month" "Payee" and "Amount" (which is the total for each payee in a given month), but that is not helpful for making a chart because there are multiple rows for each month (one for each payee). I need to get the total for the payees into columns with 1 row for each month to make the Data Series for the chart.

What I'd really like is something where I have the following:
"Year" "Month" "Monthly sum for payee1 and payee2" "Monthly sum for payee3 and 4" "Monthly sum for payee5" etc... so I can easily make the graph in OOO. Can anyone help me with this? Is this even possible? Thanks!

[Nikolay]

Hi,

Try t ouse the same idea like in my last post here:
viewtopic.php?f=12&t=764
left join will help

[Nikolay]

Code: Select all

select a.year, a.month, ifnull (p1.total,0) "payee1", ifnull (p2.total,0) "payee2" from (SELECT "year", "month" FROM "alldata" WHERE "category" = '1_Продукты' AND "subcategory" = 'Еда' GROUP BY "year", "month" ORDER BY "year", "month") a
left join (SELECT year, month, "payee", SUM( "amount" ) total FROM "alldata" WHERE payee='Коля' and "category" = '1_Продукты' AND "subcategory" = 'Еда'  GROUP BY year, month, payee ORDER BY "year", "month", "payee") p1 on p1.month=a.month and p1.year=a.year
left join (SELECT year, month, "payee", SUM( "amount" ) total FROM "alldata" WHERE payee='Маша' and "category" = '1_Продукты' AND "subcategory" = 'Еда'  GROUP BY year, month, payee ORDER BY "year", "month", "payee") p2 on p2.month=a.month and p2.year=a.year


[omalleypat]

Nikolay,

I saw in one of your other SQL Reports (the Income vs Expenses one), you used strftime to get the date formatted in YYYY-MM format. I tried to replicate that with this one, but I kept getting syntax errors. If it isn't too much trouble, could you help modify this one so that instead of the first two columns being Year and Month, there is one column formatted YYYY-MM?

Pat

[Nikolay]

Code: Select all

  select  strftime('%Y-%m',a.date), a.year, a.month, ifnull (p1.total,0) "payee1", ifnull (p2.total,0) "payee2" from (SELECT date, "year", "month" FROM "alldata" WHERE "category" = '1_Продукты' AND "subcategory" = 'Еда' GROUP BY "year", "month" ORDER BY "year", "month") a
left join (SELECT  year, month, "payee", SUM( "amount" ) total FROM "alldata" WHERE payee='Коля' and "category" = '1_Продукты' AND "subcategory" = 'Еда'  GROUP BY year, month, payee ORDER BY "year", "month", "payee") p1 on p1.month=a.month and p1.year=a.year
left join (SELECT  year, month, "payee", SUM( "amount" ) total FROM "alldata" WHERE payee='Маша' and "category" = '1_Продукты' AND "subcategory" = 'Еда'  GROUP BY year, month, payee ORDER BY "year", "month", "payee") p2 on p2.month=a.month and p2.year=a.year

[omalleypat]

Works perfect - thanks again. I was going about it all wrong.

Pat