Link to home
Start Free TrialLog in
Avatar of stl-it
stl-it

asked on

Displaying results on one line in query results

I am using the query below to return the number of customers that are labeled as A, B, C, D.  When I run my query it puts A on one line, and B on another like below.

32      A      Bill Smith
49      B      Bill Smith
37      C      Bill Smith
188      D      Bill Smith
22      A      Mike Jones
6      B      Mike Jones
41      C      Mike Jones
274      D      Mike Jones

Ideally I would like it to be like
32   49  37  188    Bill Smith
22    6    41  274   Mike Jones

I don't have any idea how to approach this.
select count(*) as Total, hi_customerratingname as Rating,owneridname as SE 
from filteredaccount
join filteredsystemuser
on (filteredaccount.ownerid =filteredsystemuser.systemuserid
and filteredsystemuser.title = 'Sales Engineer')
Where hi_customerratingname in ('a', 'b', 'c', 'd')
and filteredaccount.statuscodename = 'active'
 
group by owneridname, hi_customerratingname 
order by owneridname asc

Open in new window

Avatar of folderol
folderol

select
case Rating when 'A' then Total else 0 end as A,
case Rating when 'B' then Total else 0 end as B,
case Rating when 'C' then Total else 0 end as C,
case Rating when 'D' then Total else 0 end as D,
SE
from
(
  select count(*) as Total, hi_customerratingname as Rating,owneridname as SE
  from filteredaccount
  join filteredsystemuser
  on (filteredaccount.ownerid =filteredsystemuser.systemuserid
  and filteredsystemuser.title = 'Sales Engineer')
  Where hi_customerratingname in ('a', 'b', 'c', 'd')
  and filteredaccount.statuscodename = 'active'
  group by owneridname, hi_customerratingname
)
group by SE
order by SE asc
Wow, not concentrating today :)  This is better....

select
sum(case Rating when 'A' then Total else 0 end) as A,
sum(case Rating when 'B' then Total else 0 end) as B,
sum(case Rating when 'C' then Total else 0 end) as C,
sum(case Rating when 'D' then Total else 0 end) as D,
SE
from
(
  select count(*) as Total, hi_customerratingname as Rating,owneridname as SE
  from filteredaccount
  join filteredsystemuser
  on (filteredaccount.ownerid =filteredsystemuser.systemuserid
  and filteredsystemuser.title = 'Sales Engineer')
  Where hi_customerratingname in ('a', 'b', 'c', 'd')
  and filteredaccount.statuscodename = 'active'
  group by owneridname, hi_customerratingname
)
group by SE
order by SE asc
Avatar of stl-it

ASKER

That code makes sense but it's giving me a Msg 156, Level 15, State 1, Line 18
Incorrect syntax near the keyword 'group'.
ASKER CERTIFIED SOLUTION
Avatar of folderol
folderol

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of stl-it

ASKER

perfect!!!!!  Wow, that taught me a lot. Thanks
Avatar of stl-it

ASKER

If I were to include accounts that had no rating would I do something like this?
select 
sum(case tempresult.Rating when 'A' then Total else 0 end) as A,
sum(case tempresult.Rating when 'B' then Total else 0 end) as B,
sum(case tempresult.Rating when 'C' then Total else 0 end) as C,
sum(case tempresult.Rating when 'D' then Total else 0 end) as D,
sum(case tempresult.Rating when 'null' then Total else 0 end) as Z,
tempresult.SE
from
(
  select count(*) as Total, hi_customerratingname as Rating,owneridname as SE 
  from filteredaccount
  join filteredsystemuser
  on (filteredaccount.ownerid =filteredsystemuser.systemuserid
  and filteredsystemuser.title = 'Sales Engineer')
  Where filteredaccount.statuscodename = 'active'
  group by owneridname, hi_customerratingname 
)
as tempresult
group by tempresult.SE
order by tempresult.SE asc

Open in new window

Funny you should ask that, I just answered that exact question on another thread.
No, your code
sum(case tempresult.Rating when 'null' then Total else 0 end) as Z,
will not work.  Change it to
sum(case when tempresult.Rating is null then Total else 0 end) as Z,

Case must have an evaluatable expression.  Mine evaluates true or false.  Your syntax, in english, is
case undefined when undefined then ....
since null is not the same as an empty string, it is undefined.  NULL = NULL is not correct either.  You have to re-write it so it has the form TRUE = TRUE, if you want to compare two columns and you suspect both are null.  Unrelated, but you can do this
select SE = null from tempresult
which will reset SE to null, if you had a reason to do that.  Sometimes that is interesting when using Insert or Update.

Avatar of stl-it

ASKER

Gotcha.    I used the following and it all works.
select 
sum(case tempresult.Rating when 'A' then Total else 0 end) as A,
sum(case tempresult.Rating when 'B' then Total else 0 end) as B,
sum(case tempresult.Rating when 'C' then Total else 0 end) as C,
sum(case tempresult.Rating when 'D' then Total else 0 end) as D,
sum(case tempresult.Rating when 'Z' then Total else 0 end) as Blank,
tempresult.SE
from
(
  select count(*) as Total, isnull(hi_customerratingname, 'Z')  as Rating,owneridname as SE 
  from filteredaccount
  join filteredsystemuser
  on (filteredaccount.ownerid =filteredsystemuser.systemuserid
  and filteredsystemuser.title = 'Sales Engineer')
  Where filteredaccount.statuscodename = 'active'
  group by owneridname, hi_customerratingname 
)
as tempresult
group by tempresult.SE
order by tempresult.SE asc

Open in new window

Your approach is the best, always better to eliminate the nulls at the source, sort of like termites!
Avatar of stl-it

ASKER

thanks again