I am trying to have the total "amount" with "type" = 1 and with type = 2 by year of the "created_at" field.
Here is the table
+-----------------+------------------+------+
| Field | Type | Null |
+-----------------+------------------+------+-----
| id | int(10) unsigned | NO |
| type | int(10) unsigned | YES |
| type | varchar(255) | NO |
| amount | double(11,2) | NO |
| created_at | timestamp | YES |
| updated_at | timestamp | YES |
Here is my code (It only gives the total for type = 1):
DB::select(
'SELECT
year,
created_at,
type,
SUM(amount) AS total
FROM
(SELECT
YEAR(created_at) AS year,
amount,
created_at,
type
FROM table
WHERE type= 1) AS t
GROUP BY year, type
ORDER BY year;
');
thanks in advance for any help.
I suspect you really want conditional aggregation:
SELECT YEAR(created_at) AS year,
SUM(CASE WHEN type = 1 THEN amount ELSE 0 END) as amount_1,
SUM(CASE WHEN type = 2 THEN amount ELSE 0 END) as amount_2
FROM table
GROUP BY YEAR(created_at);
Your question needs more clarification but I assumed you wanted to compare type 1 amount per year,created at, type to the total yearly amount.
select year(created_at), created_at, type,
sum(case when type =1 then amount else 0 end) as type1amount,
sum(amount) over (partition by year(created_at)) as yeartotal
from table
group by 1,2,3
You just need one simple query for this. Take the year of the date, sum the amount and group by year and type. FYI, you have type as a field twice in your question. That's not possible, so I'm assuming that it's really some other field name the second time.
SELECT
YEAR(created_at) AS year,
type,
SUM(amount) AS amount
FROM mytable
GROUP BY YEAR(created_at), type
ORDER BY YEAR(created_at)