Mc Feranco Mc Feranco - 1 year ago 89
MySQL Question

mysql: i want to include a value in my row

i have a table, that has a lot of same values.
in my table i want to select distinct values based by column. my problem is including

this is my table

column column.a column.b
1 tax1 100
1 tax2 200
1 tax3 300
2 tax1 100
2 tax2 200

this is my select statement

select distinct column, sum(column.b) - column.b as amount,
(select column.b where column.a = tax2)as column.c
from table order by column

i want to include in my select statement column.b where column.a = tax2

this is the output that i expect.

column amount column.c
1 500 200
2 200 200

i just don't know i can include it in my select statement. thanks.

Answer Source

The answer to your question is to use conditional aggregation and an explicit group by. For example a reasonable query would be:

select column,
       sum(case when a <> 'tax2' then b else 0 end) as amount,
       sum(case when a = 'tax2' then b else 0 end) as c
from t
group by column;

This does not give exactly the results in your question. But, the results in your question are unstable because you are mixing sum(column.b) with column.b -- the unaggregated value is taken from an indeterminate row.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download