Multiple SELECT command in sql query

Question

I want to retrieve this kind of table in my sql:

    Type     Qad (type count)     Correct(No. of item without error)     Accuracy(%)
   type 1        3                                 3                            100
   type2         6                                 3                             50

I am curretly trying to use this query but it has an error:

SELECT `type` AS 'ReturnType', COUNT(*) AS 'QAd',
(SELECT COUNT(*) FROM bartran WHERE QASample='2' AND QAErrorID='1' AND `type`=ReturnType AND TimefileDate BETWEEN '2012-01-01' AND '2012-12-31' AS 'Correct', 
(SELECT COUNT(*) FROM bartran WHERE QASample='2' AND QAErrorID='1' AND `type`=ReturnType AND TimefileDate BETWEEN '2012-01-01' AND '2012-12-31') / COUNT(*) * 100) AS 'Accuracy' 
FROM bartran WHERE QASample='2'
AND TimefileDate BETWEEN '2012-01-01' AND '2012-12-31'
GROUP BY TYPE;

I'm current new to this one, hope someone helps me with this. thanks!!

select Type, Qad, Correct, Accuracy from bartran where QASample = '2' AND other stuff you want the data to be like GROUP BY Type; — Vogel612
– Vogel612, Commented Jan 17, 2013 at 10:13

Ankur · Accepted Answer · 2013-01-17 10:24:55Z

3

You had the brackets ) missing in the query, correct is:

SELECT `type`  AS 'ReturnType', 
       Count(*)   AS 'QAd', 
       (SELECT Count(*) 
        FROM   bartran 
        WHERE  qasample = '2' 
               AND qaerrorid = '1' 
               AND `type` = returntype 
               AND timefiledate BETWEEN '2012-01-01' AND '2012-12-31') AS 
       'Correct', 
       (SELECT Count(*) 
        FROM   bartran 
        WHERE  qasample = '2' 
               AND qaerrorid = '1' 
               AND `type` = returntype 
               AND timefiledate BETWEEN '2012-01-01' AND '2012-12-31') / Count(* 
       ) * 100 AS 'Accuracy' 
FROM   bartran 
WHERE  qasample = '2' 
       AND timefiledate BETWEEN '2012-01-01' AND '2012-12-31' 
GROUP  BY type;

Much better Query would be:

SELECT `type` AS 'ReturnType', 
       Count(*)  AS 'QAd', 
       Count(CASE 
               WHEN qaerrorid = '1' THEN 1 
               ELSE NULL 
             end)  AS 'Correct', 
       Count(CASE 
               WHEN qaerrorid = '1' THEN 1 
               ELSE NULL 
             end) / Count(*) * 100 AS 'Accuracy' 
FROM   bartran 
WHERE  qasample = '2' 
       AND timefiledate BETWEEN '2012-01-01' AND '2012-12-31' 
GROUP  BY type;

edited Jan 17, 2013 at 10:24

answered Jan 17, 2013 at 10:13

Ankur

12.8k7 gold badges40 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

jomsk1e Over a year ago

i think this now solve my problem, but why does the execution takes a lot of time? 1 minute plus. :(

Ankur Over a year ago

@JRC Try the new Query that i have added

Andrey Voloshin Over a year ago

@Ankur it a good point to use SUM instead Count SUM(CASE WHEN qaerrorid = '1' THEN 1 ELSE 0 end). Also use COUNT(1), not COUNT(*). It will be a bit faster

Ankur Over a year ago

@AndreyVoloshin count(1) and count(*) are same, see this and also I did not find anywhere if sum is better than count, so I would prefer count for code clarity point of view

Collectives™ on Stack Overflow

Multiple SELECT command in sql query

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related