SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


how to best summarize data


how to best summarize data

Author
Message
christina.honnert
christina.honnert
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 11
I’ve got the following data and I’m trying to get to the following results. I’m assuming I need to use a CASE statement, but was looking for some help and to find out the best way to optimize the query as well because I will be hitting hundreds of thousands of rows to get the summarized data.

Name Category Source UserID EnteredUserID Order #
Joe Smith MED W jsmith bob 2012-01
Joe Smith MED W jsmith jsmith 2012-02
Joe Smith LAB V jsmith jsmith 2012-03
Bob White MED PROTOCOL bob bob 2012-04
Bob White RAD W bob jsmith 2012-05
Bob White CT V bob katie 2012-06
Bob White MED MD ORDER bob jsmith 2012-07

I want to get a total count of orders by Name then by Category like below.
Name Category Total Orders % Phys Entered %Other Entered Protocol %
Joe Smith MED 2 50% 50% 0%
Joe Smith LAB 1 100% 0% 0%
Bob White MED 2 0% 50% 50%
Bob White RAD 1 0% 100% 0%
Bob White CT 1 0% 100% 0%

Total Orders = # of orders by Name & Category

% Phys Entered = # of orders where user ID = EnteredUser ID AND Source <> ‘PROTOCOL’ / Total Orders by Name & Category

%Other Entered = # of orders where userID <> EnteredUserID AND Source <> ‘PROTOCOL’/ total Orders by Name & Category

Protocol % = # of orders where Source = ‘PROTOCOL’ / Total Orders
Sean Lange
Sean Lange
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62027 Visits: 17954
christina.honnert (9/11/2012)
I’ve got the following data and I’m trying to get to the following results. I’m assuming I need to use a CASE statement, but was looking for some help and to find out the best way to optimize the query as well because I will be hitting hundreds of thousands of rows to get the summarized data.

Name Category Source UserID EnteredUserID Order #
Joe Smith MED W jsmith bob 2012-01
Joe Smith MED W jsmith jsmith 2012-02
Joe Smith LAB V jsmith jsmith 2012-03
Bob White MED PROTOCOL bob bob 2012-04
Bob White RAD W bob jsmith 2012-05
Bob White CT V bob katie 2012-06
Bob White MED MD ORDER bob jsmith 2012-07

I want to get a total count of orders by Name then by Category like below.
Name Category Total Orders % Phys Entered %Other Entered Protocol %
Joe Smith MED 2 50% 50% 0%
Joe Smith LAB 1 100% 0% 0%
Bob White MED 2 0% 50% 50%
Bob White RAD 1 0% 100% 0%
Bob White CT 1 0% 100% 0%

Total Orders = # of orders by Name & Category

% Phys Entered = # of orders where user ID = EnteredUser ID AND Source <> ‘PROTOCOL’ / Total Orders by Name & Category

%Other Entered = # of orders where userID <> EnteredUserID AND Source <> ‘PROTOCOL’/ total Orders by Name & Category

Protocol % = # of orders where Source = ‘PROTOCOL’ / Total Orders


It is not too likely you are going to get much help with your query unless you post some more details. I see you are pretty new around here. You should take a look at the first link in my signature for best practices when posting questions.

I can't provide you much detail but I don't see how CASE is going to help in your situation. What you need to do is look at aggregate data. You will need to do some grouping and use some aggregate functions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
t.brown 89142
t.brown 89142
Old Hand
Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)

Group: General Forum Members
Points: 390 Visits: 140
I think you're going to need COUNTs and GROUP BY

SELECT Name, Category, COUNT(Category) AS CountCat
FROM ..yourTable..
GROUP BY Name, Category

then add your percentages in by getting the MAX as a subquery (I'm ready to be corrected here)

SELECT Name, Category, ( 100 * COUNT(Category) / (SELECT COUNT(*) FROM ..yourTable.. ) ) AS PCNT
FROM ..yourTable..
GROUP BY Name, Category

Hopefully this will give you the framework of an approach to this problem.
Brandie Tarvin
Brandie Tarvin
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37481 Visits: 9268
Just a warning, Christina, if your data is set up the way I think it is, t.brown's solution will not immediately give you the answer you're looking for. You will definitely have to go tweaking in order to find the best way to do this.

However, if you're able to give us table DDL and an insert statement with sample data, and let us know what code you've tried to make this work, we'll help you tweak until you get it right.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
christina.honnert
christina.honnert
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 11
I did end up figuring this out by the following:

select b.Name, a.Category, COUNT(a.OrderNumber) as 'Total Orders',
((sum(
CASE
WHEN b.UserID = a.EnteredUserID and c.OrdSourceID <> 'PROTOCOL'
THEN 1
ELSE 0
END) * 100 / nullif(COUNT(a.OrderNumber),0))) as 'Phys Entered %',
((SUM(
CASE
WHEN b.UserID <> a.EnteredUserID and c.OrdSourceID <> 'PROTOCOL'
THEN 1
ELSE 0
END) * 100 / nullif(COUNT(a.OrderNumber),0))) as 'Other Entered %',
((SUM(
CASE
WHEN c.OrdSourceID = 'PROTOCOL'
THEN 1
ELSE 0
END) * 100 / nullif(COUNT(a.OrderNumber),0))) as 'Revised Order %'
from OeOrders a
INNER JOIN OeOrders2 c ON a.OrderID = c.OrderID
LEFT OUTER JOIN DMisUsers b ON a.ProviderID = b.ProviderID
where (a.OrderDateTime between '2012-01-01 00:00:00:000' AND '2012-09-09 23:59:59:999')
and a.Status <> 'CANC'
and b.Name is not null
group by b.Name, a.Category
order by b.Name, a.Category
Brandie Tarvin
Brandie Tarvin
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37481 Visits: 9268
How many statuses do you have? If it's 10 or less, your performance may be increased if you switch that WHERE clause to say something like Status IN ('Active','Expired',...'n').

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search