Lia Nungaray
asked on
How to summarize data with MSSQL
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Worked great. Thanks!
here it is:
there is a issue with pages, I guess you calculated wrong!
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
there is a issue with pages, I guess you calculated wrong!
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...
Open in new window
Open in new window
Open in new window
Try the following...
Open in new window
...returns this...
Open in new window