2

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`
Shiro
  • 7,344
  • 8
  • 46
  • 80
  • the sql statement and table a bit different due to I lazy to retype it again :p – Shiro Jan 05 '12 at 08:07
  • so which one are you trying to get an answer for? The example tables or the tables and columns referenced in the query that you have tried? If you need a definitive answer I suggest you take the time to make your example table layout and data and your example query match. – Tom Mac Jan 05 '12 at 08:26
  • Hi @Tom Mac, I had updated the sql statement to match the case. Thanks! – Shiro Jan 05 '12 at 08:41

1 Answers1

3

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.

Tom Mac
  • 9,693
  • 3
  • 25
  • 35
  • Hi @Tom Mac, really appreciate your solution. My MySQL knowledge told me that my question only can be solved with join table, but you give a new solution and view to me. Thanks a lot! – Shiro Jan 05 '12 at 08:27