Solved

How to summarize data with MSSQL

Posted on 2012-03-29
4
471 Views
Last Modified: 2012-03-29
Hello Experts,

I am having trouble creating a query that will summarize data from a few tables that store sent fax history... Here's the basic query:

SELECT *
FROM documents doc
JOIN history hist ON hist.id = doc.docid
JOIN historydetails hd ON hd.id = hist.id

The documents table contains all documents to be sent. Table historydetails has a row for each document being sent out. So, if a document was sent without any errors, there will only be one row in historydetails per document. Now, if there were errors, more than one row in historydetails will be associated with documents. Column status in historydetails determines if an error has been sent. If status equals 5, document was sent successfully. Also, pages indicates number of pages sent out. So for example,

ID     Status    Pages
1       3            4
1       1            2
1       5            6
2       5            8
3       4            0
3       1            0
3       6            1

This table shows that document ID 1 had three attempts, 2 failed, 1 was successful, and in total 10 pages were sent. Document ID 2 had 1 successfull attempt, 8 pages sent. Document ID 3 has 3 attempts, none successful, 1 page sent. What I would like to do, is summarize the data into something like this:

Documents ID           Attempts   Failed      Successful Pages
1                                3                2             1               10
2                                1                0             1                8
3                                3                3             0                1

I appreciate any help provided... Thanks.
0
Comment
Question by:horalia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 37783168
Hi.

You can use conditional aggregation to achieve this.

SELECT [Document ID] = doc.docid
     , Attempts = COUNT(*)
     , Failed = SUM(CASE hd.Status WHEN 5 THEN 0 ELSE 1 END)
     , Successful = COUNT(CASE hd.Status WHEN 5 THEN 1 END)
     , Pages = SUM(hd.Pages)
FROM documents doc
JOIN history hist ON hist.id = doc.docid
JOIN historydetails hd ON hd.id = hist.id
GROUP BY doc.docid
;

Open in new window


EDIT: ensure to correctly identify which table each column is coming from.
0
 
LVL 6

Expert Comment

by:netjgrnaut
ID: 37783245
document ID 1 had three attempts, 2 failed, 1 was successful, and in total 10 pages were sent.
...but...
ID     Status    Pages
1       3            4
1       1            2
1       5            6
4+2+6=12
How did you come up with 10 pages?

One possible answer to the problem (assuming that you wanted 12 pages for ID 1, not 10) is to build multiple temp tables in your query and do left joins to return a full set.

Given the following tables...
dbo.documents
docid	docname
1	doc1
2	doc2
3	doc3

Open in new window

dbo.history
id	historydata
3	hist3
2	hist2
1	hist1

Open in new window

dbo.historydetails
id	status	pages
1	3	4
1	1	2
1	5	6
2	5	8
3	4	0
3	1	0
3	6	1

Open in new window


Try the following...
SELECT doc.docid, doc.docname, hist.historydata, hd1.attempts, hd2.failed, hd3.successful, hd4.pages
FROM documents doc
JOIN history hist ON hist.id = doc.docid
left JOIN (select id, count(*) as attempts from historydetails group by id) hd1 ON hd1.id = doc.docid
left JOIN (select id, count(status) as failed from historydetails where status!=5 group by id) hd2 ON hd2.id = doc.docid
left JOIN (select id, count(status) as successful from historydetails where status=5 group by id) hd3 ON hd3.id = doc.docid
left JOIN (select id, sum(pages) as pages from historydetails group by id) hd4 ON hd4.id = doc.docid

Open in new window


...returns this...
docid	docname	historydata  attempts  failed	successful  pages
1	doc1	hist1	    3	    2	1	  12
2	doc2	hist2	    1	    NULL	1	  8
3	doc3	hist3	    3	    3	NULL	  1

Open in new window

0
 

Author Closing Comment

by:horalia
ID: 37783312
Worked great. Thanks!
0
 
LVL 55

Expert Comment

by:Huseyin KAHRAMAN
ID: 37783350
here it is:

with s as (
select 1 id, 3 status, 4 pages
union all select 1,1,2
union all select 1,5,6
union all select 2,5,8
union all select 3,4,0
union all select 3,1,0
union all select 3,6,1
)
select id, COUNT(1) attempt, 
  SUM (case when status!=5 then 1 else 0 end) Failed,
  SUM (case when status=5 then 1 else 0 end) Successful,
  SUM (Pages) Pages
from s
group by id
order by id

id	attempt	Failed	Successful	Pages
1	3	2	1	12
2	1	0	1	8
3	3	3	0	1

Open in new window


there is a issue with pages, I guess you calculated wrong!
0

Featured Post

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to create custom column layout styles for Bootstrap. The article uses 5 columns to illustrate the concept, but the principle can be extended to any number of columns.
This article discusses four methods for overlaying images in a container on a web page
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question