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 2000 Forums
 SQL Server Development (2000)
 About a group by query to find percents

Author  Topic 

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2006-09-28 : 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)

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
Go to Top of Page

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 out

Ashley Rhodes
Go to Top of Page

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
Go to Top of Page

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2006-09-28 : 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
Go to Top of Page

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 code
thats what i meant. I hope you can still help

Ashley Rhodes



what do you have so far?



-ec
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2006-09-29 : 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
Go to Top of Page

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
Go to Top of Page

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."
Go to Top of Page
   

- Advertisement -