query question - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

query question

Author

Topic

aikman
Starting Member

22 Posts

Posted - 2008-07-21 : 11:44:33

Hi, in the query i don't get the salespersons that have zero customers, how do I get that in my table?

select personal_id as sale, rtrim(p.surename) + ' ' + rtrim(p.lastname) as name, count(salesperson_id) as 'number of customer' if number of customer= 0
from personal p
join customer c on c.salesperson_id = p.personal_id

group by personal_id, p.surename, p.lastname, salesperson_id
order by 'number of customer' desc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-21 : 11:52:54

[code]select personal_id as sale, rtrim(p.surename) + ' ' + rtrim(p.lastname) as name,
count(salesperson_id) as 'number of customer'
from personal p
left join customer c on c.salesperson_id = p.personal_id

group by personal_id, name
order by 'number of customer' desc[/code]

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2008-07-21 : 11:53:13

instead of

join customer c on c.salesperson_id = p.personal_id

use

left outer join customer c on c.salesperson_id = p.personal_id

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2008-07-21 : 11:54:25

I should have refreshed :)

aikman
Starting Member

22 Posts

Posted - 2008-07-21 : 12:06:30

and if I instead of 0 want the text "no customers"?

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2008-07-21 : 12:09:20

if you want to bring 'no customers' in the same column as count(salesperson_id), then the count will have to be converted to a non-numeric data type, is that ok with you?

aikman
Starting Member

22 Posts

Posted - 2008-07-21 : 12:22:10

yes,

i want it to look like this:

salesperson_id name number of customer
1 adam smith 54
2 carl andersen no customer

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2008-07-21 : 12:29:48

use a CASE expression

aikman
Starting Member

22 Posts

Posted - 2008-07-21 : 12:45:17

don´t understand how i can use that

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2008-07-21 : 12:50:17

read about it

http://www.craigsmullins.com/ssu_0899.htm

aikman
Starting Member

22 Posts

Posted - 2008-07-21 : 12:56:46

don´t under stand if i have 50 sales persons that have different numbers of customers, then i have to do allot of else in the case senario, and I have to update when a customers get more customers. I want it to show number of customers per sales persons, and when a sales person don´t have any customers it should display "No Customers"

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2008-07-21 : 13:12:58

SELECT SALE, NAME, COALESCE(NULLIF(CONVERT(varchar(), [NUMBER OF CUSTOMER]), '0'), 'no customer') AS 'no of customer' FROM
(select personal_id as sale, rtrim(p.surename) + ' ' + rtrim(p.lastname) as name,
count(salesperson_id) as 'number of customer'
from personal p
left join customer c on c.salesperson_id = p.personal_id
group by personal_id, name) a

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-21 : 13:46:54

quote:
Originally posted by rohitkumar

SELECT SALE, NAME, COALESCE(NULLIF(CONVERT(varchar(), [NUMBER OF CUSTOMER]), '0'), 'no customer') AS 'no of customer' FROM
(select personal_id as sale, rtrim(p.surename) + ' ' + rtrim(p.lastname) as name,
count(salesperson_id) as 'number of customer'
from personal p
left join customer c on c.salesperson_id = p.personal_id
group by personal_id, name) a

remember to specify a length whenever you're converting to varchar

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources