Data :                                      Desired result:

class    type    number                     class    rate   score
-------------------------                   ----------------------    
2021      1         5                        2021    0.5      4.8
2021      1         4.6                      2022    0.5      4.6
2021      0         4.8                      
2021     null       null
2022      1         4.2
2022      1         5
2022      0         4.2
2022     null       null

1. rate = (type = 1 / all list) group by class.
2. score = AVG(number) where type = 1 group by class.

I want to do like below:

SELECT
    a.class, SUM(type) / COUNT(*) AS rate, b.score
FROM
    data as a
LEFT JOIN
    (SELECT
         class, AVG(number) AS score 
     FROM
         data 
     WHERE
         type = 1 
     GROUP BY 
         class) AS b ON a.class = b.class
GROUP BY
    class

Is there any method to do this without JOIN?

CodePudding user response：

First some issues should be named:

• Do not use SQL key words like type or number as column names or table names.
• Do not do a division without ruling out possible dividing by zero exceptions.

Anyway, in case your description is correct, you can do following:

SELECT class, 
ROUND(AVG(CAST(COALESCE(type,0) AS FLOAT)),2) AS rate,
ROUND(AVG(CASE WHEN type = 1 THEN COALESCE(number,0) END),2) AS score
FROM data 
GROUP BY class;

You can see here it's working correctly: db<>fiddle

Some explanations:

• AVG will build the average without doing risky divisions.
• COALESCE replaces NULL values by zero to make sure the average will be correct.
• ROUND makes sure the average will be shown as example as 0.33, not as 0.33333...

If this is not sufficient for you, please be more precise about what exactly you want to do.