CODEWITHSUNDEEP
mysqljoingroup-by
Shiro
Shiro

Reputation: 7544

MySQL join same table display rows in columns way (complicated)

I would like to join the same table 4 times to get columns way to display, I am not sure is it possible do in 1 single SQL statements.

tbl_username 

id  username
1   Adam
2   Bob
3   Chris

tbl_opportunity

tbl_opportunity

I got these two table, I would like the outcome like this

enter image description here

I tested with following sql, but the outcome is wrong

SELECT users.`username`, COUNT(`proposal`.name) AS `Pro Total Number Quotation`, CONCAT('RM', FORMAT(SUM(`proposal`.`amount`), 2)) AS pro_rm_amount, 
COUNT(`nego`.name) AS `nego Total Number Quotation`, CONCAT('RM', FORMAT(SUM(`nego`.`amount`), 2)) AS nego_rm_amount, 
COUNT(`cw`.name) AS `cw Total Number Quotation`, CONCAT('RM', FORMAT(SUM(`cw`.`amount`), 2)) AS cw_rm_amount,
COUNT(`cl`.name) AS `cl Total Number Quotation`, CONCAT('RM', FORMAT(SUM(`cl`.`amount`), 2)) AS cl_rm_amount

FROM tbl_username users
JOIN `tbl_opportunity` AS proposal ON (proposal.`user_id` = users.id AND proposal.`sales_stage` = 'Proposal' AND proposal.`deleted`=0 AND MONTH(`proposal`.date)= '1'
AND YEAR(`proposal`.date)= '2012')
LEFT JOIN `tbl_opportunity` AS nego ON (proposal.`user_id` = users.id AND proposal.`sales_stage` = 'Nego' AND nego.`deleted`=0 AND MONTH(`nego`.date)= '1'
AND YEAR(`nego`.date)= '2012')
LEFT JOIN `tbl_opportunity` AS cw ON (cw.`user_id` = users.id AND cw.`stage` = 'Win' AND cw.`deleted`=0 AND MONTH(`cw`.date)= '1'
AND YEAR(`cw`.date)= '2012')
LEFT JOIN `tbl_opportunity` AS cl ON (cl.`user_id` = users.id AND cl.`stage` = 'Lose' AND cl.`deleted`=0 AND MONTH(`cl`.date)= '1'
AND YEAR(`cl`.date)= '2012')

GROUP BY users.`username`

Upvotes: 2

Views: 1052

Answers (1)

Tom Mac
Tom Mac

Reputation: 9853

I'm a little confused since the tables and columns that you have provided don't quite match those in the SQL that you have tried. Anyway, given the data & tables you have provided and the outcome you have specified this should help you out. Even if it doesn't it should serve as an example of how to use SUM in conjunction with CASE (a very handy SQL combination for flattening out data).

Give this a go:

select u.username,
sum(case when lower(stage) = 'proposal' then 1 else 0 end) as "Proposal Count"  ,
sum(case when lower(stage) = 'proposal' then amount else 0 end) as "Proposal Amount",
sum(case when lower(stage) = 'nego' then 1 else 0 end) as "Nego Count"  ,
sum(case when lower(stage) = 'nego' then amount else 0 end) as "Nego Amount",
sum(case when lower(stage) = 'win' then 1 else 0 end) as "Win Count"  ,
sum(case when lower(stage) = 'win' then amount else 0 end) as "Win Amount",
sum(case when lower(stage) = 'lose' then 1 else 0 end) as "Lose Count"  ,
sum(case when lower(stage) = 'lose' then amount else 0 end) as "Lose Amount"
from tbl_username u
inner join tbl_opportunity o on u.id = o.user_id
group by u.username;

Hope it helps.

Upvotes: 3

Related Questions