I need help with SQL. I have an sqlite table like so;
CREATE TABLE mytable (datetime DATE, type TEXT, amount REAL)
I need a query which would sum up amount for each type AND year-month (as you can see the year is also extracted since the data can span several years). I've come to something half-way, but I'm a bit rusty on SQL.
sqlite> SELECT strftime('%Y',datetime) AS year, strftime('%m',datetime) AS month, type, amount FROM mytable ;
2009|06|Type1|-1000.0
2009|06|Type1|-100.0
2009|06|Type2|-100.0
2009|07|Type1|-214.91
2009|07|Type2|-485.0
I've tried a number of combinations of SUM and GROUP BY on my query above but none of them does what I want. What I want is a result something like:
2009|06|Type1|-1100.0
2009|06|Type2|-100.0
2009|07|Type1|-214.91
2009|07|Type2|-485.0
Yes, type should be a foreign key, I simplified things to make it easier to ask the question :)
The use of COUNT() function in conjunction with GROUP BY is useful for characterizing our data under various groupings. A combination of same values (on a column) will be treated as an individual group.
Absolutely. It will result in filtering the records on your date range and then grouping it by each day where there is data.
HAVING Clause always utilized in combination with GROUP BY Clause. HAVING Clause restricts the data on the group records rather than individual records. WHERE and HAVING can be used in a single query.
SQL SUM() and COUNT() using variable SUM of values of a field or column of a SQL table, generated using SQL SUM() function can be stored in a variable or temporary column referred as alias. The same approach can be used with SQL COUNT() function too.
SELECT strftime('%Y',datetime) AS year,
strftime('%m',datetime) AS month,
type,
Sum(amount) As Amount
FROM mytable
Group By 1, 2, 3
Some DBs don't support group by index so you would have to do this.
SELECT strftime('%Y',datetime) AS year,
strftime('%m',datetime) AS month,
type,
Sum(amount) As Amount
FROM mytable
Group By strftime('%Y',datetime),
strftime('%m',datetime),
type
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With