Author |
Topic |
ashley.sql
Constraint Violating Yak Guru
299 Posts |
Posted - 2006-09-28 : 16:03:55
|
I have a table likeThere are only 2 citiesOrderID Orderdate City SalepersonID11 1/2/2005 NYC 20512 2/5/2006 CHG 20613 2/5/2003 NYC 20714 3/5/2006 CHG 20515 4/5/2006 NYC 206 I want to write a query to find out SalespersonID, count(orders by salespersonid), overall % of orders that sales person placed, % of his NYC orders from total NYC orders,% of CHG orders from total CHG ordersSay in this case total orders are 5 Total NYC orders are 3total CHG orders are 2205 did 2 orders 1 in nyc and 1 in CHG205, 2, (2*100)/5, (1*100)/3, (1*100)/2Just need the hintI will do the testing myself. I think if I get some idea I will be able to do this one.Ashley Rhodes |
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-09-28 : 16:25:05
|
Sum up amounts by city and overall in a derived table using a CASE to sum each city. This will give you a result with one row, and you can cross join to your main grouping query to do the Pct of Tptal calculations.CODO ERGO SUM |
|
|
ashley.sql
Constraint Violating Yak Guru
299 Posts |
Posted - 2006-09-28 : 16:26:08
|
can you give some sample code to help me figure outAshley Rhodes |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-09-28 : 16:38:47
|
I could, but you asked for a hint, so give it a shot yourself.CODO ERGO SUM |
|
|
ashley.sql
Constraint Violating Yak Guru
299 Posts |
Posted - 2006-09-28 : 17:19:50
|
hmmmm I need some hint in the form of codethats what i meant. I hope you can still helpAshley Rhodes |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-09-28 : 20:23:26
|
quote: Originally posted by ashley.sql hmmmm I need some hint in the form of codethats what i meant. I hope you can still helpAshley Rhodes
what do you have so far?-ec |
|
|
ashley.sql
Constraint Violating Yak Guru
299 Posts |
Posted - 2006-09-29 : 10:22:07
|
I do not have anything so far as this needs to be done without using derived tables if possible.Ashley Rhodes |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-09-29 : 13:42:34
|
quote: Originally posted by ashley.sql I do not have anything so far as this needs to be done without using derived tables if possible.Ashley Rhodes
Why would doing this without a derived table be a requirement? Do you know what a derived table is?Did you even make an attempt at this, or do you just expect someone to do your work for you?CODO ERGO SUM |
|
|
ashley.sql
Constraint Violating Yak Guru
299 Posts |
Posted - 2006-09-29 : 13:50:46
|
I have to do this in MS ACCESS actuallyI need to do this without derived tables and without declaring anythingcreate table orders(orderid int, orderdate datetime, city varchar(3), salespersonid int) GO insert into orders values(11, '1/2/2005', 'NYC', 205) insert into orders values(12, '2/5/2006', 'CHG', 206) insert into orders values(13, '2/5/2003', 'NYC', 207) insert into orders values(14, '3/5/2006', 'CHG', 205) insert into orders values(15, '4/5/2006', 'NYC', 206) GO declare @total int select @total=count(salespersonid)from ordersselect salespersonid, sales=count(salespersonid), percentage=count(salespersonid)*100/@total, nyc=sum(case when city='nyc' then 1*100/@total else 0 end), chg=sum(case when city='chg' then 1*100/@total else 0 end) from ordersgroup by salespersonid godrop table ordersAshley Rhodes |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-09-29 : 14:14:57
|
If you have to do this in Access, why did you post T-SQL code?You should post your question on the Access forum.CODO ERGO SUM |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-10-03 : 12:58:44
|
You can do derived tables in Access.Post your question on the MSAccess section of DBFORUMS.COM"Once you establish possibility, all that remains are desire and determination." |
|
|
|