SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 About a group by query to find percents
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ashley.sql
Constraint Violating Yak Guru

USA
299 Posts

Posted - 09/28/2006 :  16:03:55  Show Profile  Reply with Quote
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
7020 Posts

Posted - 09/28/2006 :  16:25:05  Show Profile  Reply with Quote
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

USA
299 Posts

Posted - 09/28/2006 :  16:26:08  Show Profile  Reply with Quote
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)

USA
7020 Posts

Posted - 09/28/2006 :  16:38:47  Show Profile  Reply with Quote
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

USA
299 Posts

Posted - 09/28/2006 :  17:19:50  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 09/28/2006 :  20:23:26  Show Profile  Reply with Quote
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

USA
299 Posts

Posted - 09/29/2006 :  10:22:07  Show Profile  Reply with Quote
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)

USA
7020 Posts

Posted - 09/29/2006 :  13:42:34  Show Profile  Reply with Quote
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

USA
299 Posts

Posted - 09/29/2006 :  13:50:46  Show Profile  Reply with Quote
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)

USA
7020 Posts

Posted - 09/29/2006 :  14:14:57  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 10/03/2006 :  12:58:44  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000