CODEWITHSUNDEEP
mysqldatabase
gmhk
gmhk

Reputation: 15960

Query with multiple subqueries required in Mysql

I am looking for some query help

here is the following table data

Name    Runs    Status
Ram         50  out
Ram         103 not out
Krish   51  out
Sam         15  out
Ram         15  out
Krish   78  not out

I am expecting a single query to give the folllowing results

Name    Total   >100    >50&<100    TotalTimes  Notout 
Ram        168  1           1         3          1
Sam        15   0           0         1          0
Krish      129  0           2         2          1

I am able to write the query to get the total, Totaltimes with the help of Group By functionalities, I am stuck with the rest

Here is the query I have come up

select Name, sum(Runs) as total, count(*) as totalTimes
from tempTable 
where classID IN (Select classID from upcoming_Clases where classes_id=175) 
group by Name order by total desc

I am using the Mysql Database

Upvotes: 0

Views: 147

Answers (2)

eggyal
eggyal

Reputation: 125925

You can use SUM() together with IF() to test your criteria:

SELECT
    Name,
    SUM(Runs) AS Total,
    SUM(IF(Runs>100, 1, 0)) AS `>100`,
    SUM(IF(Runs>50 AND Runs<100), 1, 0) AS `>50&<100`,
    COUNT(*) AS TotalTimes,
    SUM(IF(Status='not out', 1, 0)) AS Notout
FROM tempTable
WHERE classID IN (SELECT classID FROM upcoming_Clases WHERE classes_id = 175)
GROUP BY Name
ORDER BY Total DESC

Upvotes: 3

user359040
user359040

Reputation:

You can do this using case:

select Name, 
       sum(Runs) as total, 
       count(case when Runs>100 then 1 end) `>100`,
       count(case when Runs>50 and Runs<100 then 1 end) `>50&<100`,
       count(*) as totalTimes,
       count(case when Status='not out' then 1 end) `Not Out`
from tempTable 
where classID IN (Select classID from upcoming_Clases where classes_id=175) 
group by Name order by total desc

Upvotes: 3

Related Questions