Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to best summarize data Expand / Collapse
Author
Message
Posted Tuesday, September 11, 2012 7:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 30, 2012 5:51 AM
Points: 9, 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
Post #1357413
Posted Tuesday, September 11, 2012 7:52 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:24 PM
Points: 11,990, Visits: 11,007
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 Moden's 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)
Post #1357421
Posted Tuesday, September 11, 2012 8:01 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 27, 2012 9:02 AM
Points: 114, 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.
Post #1357436
Posted Tuesday, September 11, 2012 8:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 11:55 AM
Points: 7,053, Visits: 6,214
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 Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1357455
Posted Tuesday, September 18, 2012 12:28 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 30, 2012 5:51 AM
Points: 9, 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
Post #1360935
Posted Wednesday, September 19, 2012 4:41 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 11:55 AM
Points: 7,053, Visits: 6,214
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 Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1361252
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse