समूहीकृत डेटा फ़िल्टर करना

इंटरमीडिएट SQL

Jasmin Ludolf

Data Science Content Developer, DataCamp

HAVING

SELECT 
       release_year,
       COUNT(title) AS title_count
FROM films
GROUP BY release_year
WHERE COUNT(title) > 10;

syntax error at or near "WHERE"
LINE 4: WHERE COUNT(title) > 10;
        ^

SELECT 
       release_year,
       COUNT(title) AS title_count
FROM films
GROUP BY release_year
HAVING COUNT(title) > 10;

|release_year|title_count|
|------------|-----------|
|1988        |31         |
|null        |42         |
|2008        |225        |
...

निष्पादन का क्रम

-- Written code:

SELECT 
       certification, 
       COUNT(title) AS title_count
FROM films
WHERE certification 
       IN ('G', 'PG', 'PG-13')
GROUP BY certification
HAVING COUNT(title) > 500
ORDER BY title_count DESC
LIMIT 3;

-- Order of execution:

SELECT 
       certification, 
       COUNT(title) AS title_count

FROM films

WHERE certification 
       IN ('G', 'PG', 'PG-13')

GROUP BY certification

HAVING COUNT(title) > 500

ORDER BY title_count DESC

LIMIT 3;

HAVING बनाम WHERE

WHERE अलग-अलग रिकॉर्ड फ़िल्टर करता है, HAVING समूहीकृत रिकॉर्ड फ़िल्टर करता है
साल 2000 में कौन-सी फिल्में रिलीज़ हुई थीं?

SELECT title
FROM films
WHERE release_year = 2000;

|title         |
|--------------|
|102 Dalmatians|
|28 Days       |
...

किन वर्षों में औसत फिल्म अवधि दो घंटे से अधिक थी?

HAVING बनाम WHERE

किन वर्षों में औसत फिल्म अवधि दो घंटे से अधिक थी?

SELECT release_year
FROM films

GROUP BY release_year

HAVING AVG(duration) > 120;

|release_year|
|------------|
|1954        |
|1959        |
...

अभ्यास करते हैं!

इंटरमीडिएट SQL