• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 249
  • Last Modified:

Help me please sql query

Please help

I have a table with the following information. As you can see that John Smith was checked 6 times, 2 times out of 6 he made errors, and only one was Critical.

DateProcessed      BundleCount      NumberQCed      ErrorsDesc      CriticalNonCritical      ProcessedBy
10/29/2013      97      1      No Errors      n/a      John Smith
10/31/2013      68      1      No Errors      n/a      John Smith
11/1/2013      30      1      No Errors      n/a      John Smith
11/14/2013      1      1      Error 1      Yes      John Smith
11/14/2013      1      1      Error 2      No      John Smith
10/24/2013      5      1      No Errors      n/a      John Smith

So i would like to have output like
Name         Total          ErrorsOnly         Critical
John Smith 6                2                         1
Test.accdb
0
rfedorov
Asked:
rfedorov
2 Solutions
 
Rey Obrero (Capricorn1)Commented:
test this, run Query1
Test.accdb
0
 
Tony303Commented:
Here is a working example on your data...

CREATE TABLE #Test
(DateProcessed Date NULL,
BundleCount int NULL,
NumberQCed int NULL,
ErrorsDesc VARCHAR(10) NULL,
CriticalNonCritical VARCHAR(10) NULL,
ProcessedBy VARCHAR(30) NULL)


INSERT INTO #Test (DateProcessed,BundleCount,NumberQCed,ErrorsDesc, CriticalNonCritical,ProcessedBy)
Values ('10/29/2013', 97 ,1 ,'No Errors','n/a', 'John Smith'),
('10/31/2013',      68 ,     1  ,    'No Errors' ,    'n/a',      'John Smith'),
('11/1/2013',    30  ,    1  ,    'No Errors',      'n/a',      'John Smith'),
('11/14/2013',      1,        1,      'Error 1' ,     'Yes',     'John Smith'),
('11/14/2013' ,     1,      1    ,  'Error 2'   ,   'No'     , 'John Smith'),
('10/24/2013',      5 ,     1     , 'No Errors'  ,    'n/a'   ,   'John Smith')

SELECT *
FROM #Test

SELECT ProcessedBy, SUM(Total) AS Total, SUM(ErrorsOnly) AS ErrorsOnly, SUM(Critical) AS Critical
FROM
(
SELECT ProcessedBy, COUNT(*) AS Total,
CASE WHEN ErrorsDesc <> 'No Errors' THEN 1 ELSE 0 END AS ErrorsOnly,
CASE WHEN CriticalNonCritical = 'Yes' THEN 1 ELSE 0 END AS Critical
FROM #Test
GROUP BY ProcessedBy,ErrorsDesc, CriticalNonCritical
) a
GROUP BY a.ProcessedBy

Open in new window

0
 
rfedorovAuthor Commented:
Thank you guys,
capricorn1 ---you are the best!!
 Tony303 ---it will take some time to understand!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now