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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply