May 3, 2011 at 7:57 am
I have a table and have the columns ZipCode, SalesPerson, SalesType, SalesID.
I would like to write a select statement which would give me a table with columns:
Zipcode, SalesPerson, SalesType1 count per salesperson and per zipcode, SalesType2 count per salesperson and per zipcode.
I have no clue how to do this. Please help and thanks a lot.
zipcode salesperson salestypecount(phone) salestypecount(floor)
12345 A 5 10
May 3, 2011 at 8:01 am
I am reading wrong? Looks like both count columns should have the same info?
Also the trick with CASE and any aggregate is to "reverse them" >>
SELECT SUM(CASE WHEN ConditionHERE = true THEN 1 ELSE 0 END) AS CntSomething
SELECT SUM(CASE WHEN ConditionHERE = true THEN ColumnName ELSE 0 END) AS SumSomething
May 3, 2011 at 8:19 am
I am reading wrong? Looks like both count columns should have the same info?
Also the trick with CASE and any aggregate is to "reverse them" >>
SELECT SUM(CASE WHEN ConditionHERE = true THEN 1 ELSE 0 END) AS CntSomething
SELECT SUM(CASE WHEN ConditionHERE = true THEN ColumnName ELSE 0 END) AS SumSomething
Like this?
Select zipcode, salesperson,
count salesid as total_sales,
sum (case when salestype = 'phone' then 1 else 0 end) as salestype(phone),
sum(case when salestype = 'floor' then 1 else 0 end) as salestype(floor)
groupby zipcode, salesperson
The total_sales column should match the sum of phone + floor.
May 3, 2011 at 8:25 am
I don't see your data so I can't spot the issues. Here's how I'd debug this :
Using [salestype(else)] IN the query will tell you where you're "losing" orders.
SELECT
zipcode
, salesperson
, COUNT(*) AS total_sales
, SUM(CASE WHEN salestype = 'phone' THEN 1
ELSE 0
END) AS [salestype(phone)]
, SUM(CASE WHEN salestype = 'floor' THEN 1
ELSE 0
END) AS [salestype(floor)]
, SUM(CASE WHEN salestype NOT IN ( 'floor' , 'phone' ) THEN 1
ELSE 0
END) AS [salestype(else)]
FROM
dbo.Sales
GROUP BY
zipcode
, salesperson
--Optional, but makes it easier to read
ORDER BY
zipcode
, salesperson
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy