select and count statements - 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)

select and count statements

Author

Topic

tnichols333
Starting Member

14 Posts

Posted - 2009-08-12 : 15:06:15

I have a salesperson table with salespersonid as the pk along with name...etc... and a customer table that has salespersonid in it along with acquired...etc....

I am trying to figure out how I can return one result set that has the salesperson information in it and the number of customer that have acquired = 'true' and number of customer that have acquired = 'false'.

This is what I have but obviously it doesn't link the salesperson to the customer - so all salesperson get back the same 'gotnumber' and 'lostnumber'


select *, (SELECT COUNT(customerid) FROM Customer WHERE Acquired = 'Yes' and AcquiredDate >= Convert(datetime, Convert(int, GetDate())) - 30) AS gotnumber, (SELECT COUNT(customerid) FROM Customer WHERE Acquired = 'No' and AcquiredDate >= Convert(datetime, Convert(int, GetDate())) - 30) AS lostnumber FROM SalesPerson

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-12 : 15:39:54

Does this work?


select sp.name
       ,sum(case when c.acquired = 'Yes' then 1 else 0 end) as gotNumber
       ,sum(case when c.acquired = 'No' then 1 else 0 end) as lostNumber
from   salesperson sp
join   customer c 
       on c.salespersonid = sp.salespersonid
where  datediff(day, AcquiredDate, getdate()) <= 30
group by sp.name

Be One with the Optimizer
TG

tnichols333
Starting Member

14 Posts

Posted - 2009-08-12 : 17:21:02

close enough - THANK YOU SO MUCH!!!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources