CODEWITHSUNDEEP
mysqlsql
MarSic
MarSic

Reputation: 33

view with clause for only one column

I need to create a view with 3 columns, the first two there are no problems because they are simple sums with groupings by date...for the third I need to add only some values ​​from the initial table, how can i solve? use two where? or two condition for clause? . For example:

T1
id||amount||cod_pay||reg_date
1 ||10.00 ||SDD    || 2019-08
2 ||5.00  ||BON    || 2019-08
3 ||15.00 ||SDD    || 2019-07
4 ||10.00 ||BOL    || 2019-06
5 ||40.00 ||SDD    || 2019-06
6 ||110.00||BON    || 2019-06

VIEW
reg_date||month_amount||sdd_month_amount
2019-08 ||15.00       ||10.00           
2019-07 ||15.00       ||15.00           
2019-06 ||160.00      ||40.00

I don't know if it's clear, I hope so. Thanks for the support.

Upvotes: 0

Views: 37

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Use conditional aggregation:

select reg_date, sum(amount) as month_amount,
       sum(case when cod_pay = 'SDD' then amount end) as sdd_amount
from t
group by reg_date;

Here is a db<>fiddle, illustrating that it works.

Upvotes: 1

Related Questions