July 19, 2011 at 5:56 am
Hi Guys,
I am trying to get this result, can anyone give a lead or clue how to achieve this?
Rep # of AuditsFinal StatusReasons Description
Davis, David 23 Pass Matching Iconic
Davis, David 12 InconclusiveCould not find location
Davis, David 4 Inconclusive
Davis, David 67 Pass
Davis, David 43 Fail FMR: Parity & Auditor: Clean Store
Davis, David 21 Fail
Davis, David 12 Pass
Davis, David 9 Pass
Davis, David 34 Pass
(Total Audits:) 225
(Total Passes:) 145
(Total Fails: ) 64
(Total Inconclusives:) 16
July 19, 2011 at 6:04 am
What is your table structure? Is this one column?
July 19, 2011 at 6:44 am
I'm assuming you are new to SQL, because (1) you have spaces in your column names and (2) you are asking us to list your source data together with totals having a different column structure than your source data. You aren't going to do that with one query. (3) You're creating more work by listing each of the totals on separate rows.
To get you started, you can take the total of "passes" by means of a CASE statement, like this:
SELECT SUM(CASE when [final status] = 'Pass' then [# of Audits] else 0 end) as Passes
from whateverTable
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 19, 2011 at 7:42 am
Thank you bopeavy.
My structure is like this,
[REP] [# of Audits] [Final Status] [Reasons Description]
Peter, Test 1 NULL NULL
Peter, Test 1 Fail Matching Parity
Peter, Test 1 Pass Matching Description
Peter, Test 1 Pass Matching Iconic
The REP field is a varchar, [# of Audits] field is of type bit and the remainig two fields [(Final Status] and [Reasons Descriptioon]) are varchars. I have made the field names bold for clarity. So for the rep FMR, Test, I would like to have the aggregate such that they appear as follows.
Total Audits : 4
Total Fails: 1
Total Passes: 2
I need to get the aggregates for each rep. So for the reps, Peter, Test and Joh Doe, the aggregates would be
REP # of Audits Final Status Reasons Description
Peter, Test1 NULL NULL
Peter, Test1 Fail Matching Parity
Peter, Test1 Pass Matching Description
Peter, Test1 Pass Matching Iconic
Total Audits : 4
Total Fails: 1
Total Passes: 2
John, Doe1 NULL NULL
John, Doe0 Fail Matching Parity
John, Doe0 Pass Matching Description
John, Doe1 Pass Matching Iconic
Total Audits : 2
Total Fails: 0
Total Passes: 1
Please let me know if you have any questions
July 19, 2011 at 7:45 am
Hi Dixie,
The fields you see are just aliases and not the actual field names in the table
July 19, 2011 at 8:24 am
That's fine, Theomed. I hope the CASE example was of help to you.
In the future, you might find it useful to read this article[/url] to see how to set up a problem so that people can immediately try to code and test a solution for you. The way you displayed your sample data it puts the work on the volunteers here to try to recreate your schema and data. If you take a couple of minutes to give us a CREATE TABLE statement and some INSERTs to populate it with sample data, you will find more people volunteering to offer you coded and tested solutions.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 19, 2011 at 9:39 am
Thanks Dixie. I will take that into consideration next time I post a question. I will try your solution and see if it helps.
July 19, 2011 at 9:54 am
This is what bopeavy was asking for. Dixie, I agree with you, from now on, I will comply with the rules.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE AggregateTable(
[Rep] [nvarchar](255) NULL,
[Audits] [bit] NULL,
[FinalStatus] [nvarchar](255) NULL,
[ReasonsDescription] [nvarchar](255) NULL,
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT INTO AggregateTable
(Rep, Audits, FinalStatus, ReasonsDescription)
SELECT 'John Doe',1,'Pass','This was a success' UNION ALL
SELECT 'John Doe',0,'Pass','This was a failure' UNION ALL
SELECT 'John Doe',NULL,'UNKNOWN','This was not known' UNION ALL
SELECT 'Mark Price',1,'Pass','This was a success' UNION ALL
SELECT 'John Doe',1,'Pass','This was a success' UNION ALL
SELECT 'John Doe',0,'Fail','This was a failure' UNION ALL
SELECT 'Mark Price',1,'Pass','This was a success' UNION ALL
SELECT 'Mark Price',0,'Pass','This was a success' UNION ALL
SELECT 'John Doe',1,'Pass','This was a success' UNION ALL
SELECT 'Mark Price',NULL,'UNKNOWN','This was not known' UNION ALL
SELECT 'John Doe',1,'Pass','This was a success' UNION ALL
SELECT 'Mark Price',1,'Pass','This was a success' UNION ALL
SELECT 'Mark Price',1,'Pass','This was a success' UNION ALL
SELECT 'John Doe',NULL,'UNKNOWN','This was not known' UNION ALL
SELECT 'John Doe',1,'Pass','This was a success'
July 19, 2011 at 10:23 am
Would this be what your looking for?
Select a.Rep ,a.total_audits, b.total_Passes, c.total_Failures from
( SELECT [Rep], COUNT(audits) as total_audits
FROM [master].[dbo].[AggregateTable]
group by Rep
) a join
(SELECT [Rep], COUNT(audits) as total_Passes
FROM [master].[dbo].[AggregateTable] where audits ='1'
group by Rep
) b on a.rep = b.rep join
(SELECT [Rep], COUNT(audits) as total_Failures
FROM [master].[dbo].[AggregateTable] where audits ='0'
group by Rep
) c on a.rep = c.rep
July 19, 2011 at 10:45 am
Thanks, Theomed. 🙂
They aren't rules; they are just suggestions. But they are REALLY helpful suggestions.
What you posted, enabled me to produce this in a couple of minutes for you.
I notice that in your original example [Audits] was an integer column, and that changed to a bit column in the CREATE TABLE you just posted. I've altered the CASE statements accordingly. This may or may not be what you intended. If not, just remove the references to "and Audits = 1".
select Rep
,sum(CASE WHEN Audits = 1 then 1 else 0 end) as TotalAudits
,sum(CASE WHEN FinalStatus = 'Pass' and Audits = 1 then Audits else 0 end) as TotalPasses
,sum(CASE WHEN FinalStatus = 'Fail' and Audits = 1 then Audits else 0 end) as TotalFails
,sum(CASE WHEN FinalStatus = 'Unknown' then 1 else 0 end) as TotalUnknown
from AggregateTable
group by Rep
order by Rep
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 19, 2011 at 4:46 pm
Thanks guys, I appreciate your help
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply