CODEWITHSUNDEEP
sqlsql-serversubquery
David Munsa
David Munsa

Reputation: 893

how to do math on results from two querys

i have to results that came from each query

first query:

SELECT  [xcv],  COUNT( * ) AS Total
  FROM [my_table]  
 GROUP BY [xcv]

second query:

SELECT [xcv],  COUNT( * ) AS Total
      FROM [my_table]   
    WHERE=[result]='ok'
    GROUP BY [xcv]

what i want to do is to get the value of -> [ (first_query/second query) *100 ]

And i want results for each [xcv] in my table...

any idea how i can do it ?

thank you all

Upvotes: 0

Views: 59

Answers (2)

mjsqu
mjsqu

Reputation: 5452

One query:

SELECT [xcv],
       (SUM(CASE 
                 WHEN [result] = 'ok' 
                      THEN 1 
                      ELSE 0 
                 END
           )/COUNT( * )
        ) * 100
  FROM [my_table]
GROUP BY [xcv]

Upvotes: 0

Alexander
Alexander

Reputation: 3179

SELECT t1.[xcv], 
       (t1.total/t2.total)*100
  FROM (SELECT [xcv],  
               COUNT( * ) AS Total
          FROM [my_table]  
        GROUP BY 
               [xcv]
        ) t1 JOIN (
                   SELECT [xcv], 
                          COUNT( * ) AS Total
                     FROM [my_table]   
                    WHERE [result]='ok'
                    GROUP BY 
                          [xcv]
                   ) t2
                  ON t1.[xcv]=t2.[xcv]

I also removed the = sign from WHERE=[result]='ok' - I think you mistyped it in.

Upvotes: 1

Related Questions