CODEWITHSUNDEEP
mysql
user858982
user858982

Reputation: 15

Query without using GROUP_CONCAT

Having trouble with querying database. 

Id parent pro_comp_id    year         type  financial  quantity

1   1           2      2011-08-15        1     10           12
2   1           2      2011-08-15        2     100         120 
3   1           2      2011-08-15        3     178         190

can any body tell me how to query this so that it shows this three rows in one row.

financial  quantity financial  quantity financial  quantity 
  10      12          100     120           178    190

it can be done using the group concat SELECT GROUP_CONCAT(financial),parent from achivements group by parent but I don't want to use Group_concat

Upvotes: 0

Views: 127

Answers (1)

Bohemian
Bohemian

Reputation: 425198

What you want is generally called a "pivot". Here's the standard way of doing that:

select
  year,
  sum((financial = 10) * quantity) as financial_10_qty,
  sum((financial = 100) * quantity) as financial_100_qty,
  sum((financial = 178) * quantity) as financial_178_qty
from mytable
group by 1;

This works because financial = ? is 1 for true and 0 for false.

This answer assumes that the values of "financial" are known and fixed.

Upvotes: 2

Related Questions