Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
thehandsomecode
Yak Posting Veteran
51 Posts |
 Posted - 2007-05-05 : 07:56:13
|
| i currently work with a microsoft crm which works over an sql database. however i need to pull out some reports but i am having a hard time.Sales Person Leadname adress rating------------ -------- ----- ------john doe mary doe 12 1st street hotjane doe example 21 mary st coolhow do i pull out the total of hot or cold ratings per sales person and present all the information on one table.Please helpMelvin FelicienIT ManagerDCG Properties Limited |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-05-05 : 08:03:08
|
[code]select [Sales Person], total_hot = sum(case when rating = 'hot' then 1 else 0 end), total_cool = sum(case when rating = 'cool' then 1 else 0 end)from tablegroup by [Sales Person][/code]
KH |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-05-05 : 08:07:05
|
| Otherwise post some sample data with expected resultMadhivananFailing to plan is Planning to fail |
|
|
|
thehandsomecode
Yak Posting Veteran
51 Posts |
Posted - 2007-05-05 : 08:13:43
|
| Thank you so much. this is exactly what i am looking for. thank you guys for the quick response.one more question. if i were to want to add the total of all the ratings per sales person eg sum of hot+cool and total them. how do i achieve this.Melvin FelicienIT ManagerDCG Properties Limited |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-05-05 : 08:18:29
|
[code]select [Sales Person], total_hot = sum(case when rating = 'hot' then 1 else 0 end), total_cool = sum(case when rating = 'cool' then 1 else 0 end), total_rating = count(*)from tablegroup by [Sales Person][/code]
KH |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
thehandsomecode
Yak Posting Veteran
51 Posts |
Posted - 2007-05-05 : 08:25:14
|
| This works perfectly. Thank you muchMelvin FelicienIT ManagerDCG Properties Limited |
|
|
|
|
|
|
Leads by rating Breakdown