| Author |
 Topic  |
|
|
ashley.sql
Constraint Violating Yak Guru
USA
299 Posts |
 Posted - 09/28/2006 : 16:03:55
|
I have a table like
There are only 2 cities
OrderID Orderdate City SalepersonID
11 1/2/2005 NYC 205
12 2/5/2006 CHG 206
13 2/5/2003 NYC 207
14 3/5/2006 CHG 205
15 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 orders
Say in this case total orders are 5 Total NYC orders are 3
total CHG orders are 2
205 did 2 orders 1 in nyc and 1 in CHG
205, 2, (2*100)/5, (1*100)/3, (1*100)/2
Just need the hint
I 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)
USA
6997 Posts |
Posted - 09/28/2006 : 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
USA
299 Posts |
Posted - 09/28/2006 : 16:26:08
|
can you give some sample code to help me figure out
Ashley Rhodes |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 09/28/2006 : 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
USA
299 Posts |
Posted - 09/28/2006 : 17:19:50
|
hmmmm I need some hint in the form of code
thats what i meant. I hope you can still help
Ashley Rhodes |
|
|
|
eyechart
Flowing Fount of Yak Knowledge
USA
3575 Posts |
Posted - 09/28/2006 : 20:23:26
|
quote:
Originally posted by ashley.sql
hmmmm I need some hint in the form of code
thats what i meant. I hope you can still help
Ashley Rhodes
what do you have so far?
-ec |
|
|
|
ashley.sql
Constraint Violating Yak Guru
USA
299 Posts |
Posted - 09/29/2006 : 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)
USA
6997 Posts |
Posted - 09/29/2006 : 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
USA
299 Posts |
Posted - 09/29/2006 : 13:50:46
|
I have to do this in MS ACCESS actually
I need to do this without derived tables and without declaring anything
create 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 orders
select 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 orders
group by salespersonid
go
drop table orders
Ashley Rhodes |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 09/29/2006 : 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
Flowing Fount of Yak Knowledge
USA
2365 Posts |
Posted - 10/03/2006 : 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." |
|
|
| |
Topic |
|
About a group by query to find percents
