डेटा के समूह बनाना

Oracle SQL परिचय

Hadrien Lacroix

Content Developer

डेटा ग्रुप करना

डेटा एग्रीगेट करना

समूह जानकारी

| Composer        | Milliseconds |
|-----------------|--------------|
| Antonio Vivaldi | 199,086      |
| Pearl Jam       | 122,801      |
| Pearl Jam       | 65,593       |
| Jimmy Page      | 401,920      |
| Jimmy Page      | 386,063      |
| Jimmy Page      | 132,702      |
| Jimmy Page      | 189,675      |
| Jimmy Page      | 126,641      |
| Carlos Santana  | 318,432      |
| Carlos Santana  | 296,437      |
| Carlos Santana  | 882,834      |
| ...             | ...          |

प्रत्येक composer के गानों की औसत track length क्या है?

GROUP BY
- टेबल की rows को समूहों में बाँटें
- हर समूह का सारांश पाने के लिए group functions का उपयोग करें

GROUP BY

SELECT Composer, AVG(Milliseconds)
FROM Track
GROUP BY Composer

| Composer        | AVG(Milliseconds) |
|-----------------|-------------------|
| Antonio Vivaldi | 199,086.0         |
| Pearl Jam       | 94,197.0          |
| Jimmy Page      | 474,888.3         |
| Carlos Santana  | 499,234.3         |
| ...             | ...               |

GROUP BY और WHERE

SELECT Composer, AVG(Milliseconds)
FROM Track
WHERE Genre = 1
GROUP BY Composer

| Composer        | AVG(Milliseconds) |
|-----------------|-------------------|
| Antonio Vivaldi | 199,086.0         |
| Pearl Jam       | 94,197.0          |
| Jimmy Page      | 474,888.3         |
| Carlos Santana  | 499,234.3         |
| ...             | ...               |

GROUP BY और ORDER BY

SELECT Composer, AVG(Milliseconds) AS Average
FROM Track
GROUP BY Composer
ORDER BY AVG(Milliseconds)

| Composer        | AVG(Milliseconds) |
|-----------------|-------------------|
| Pearl Jam       | 94,197.0          |
| Antonio Vivaldi | 199,086.0         |
| Jimmy Page      | 474,888.3         |
| Carlos Santana  | 499,234.3         |
| ...             | ...               |

GROUP BY और ORDER BY

SELECT Composer, AVG(Milliseconds) AS Average
FROM Track
GROUP BY Composer
ORDER BY 2

| Composer        | AVG(Milliseconds) |
|-----------------|-------------------|
| Pearl Jam       | 94,197.0          |
| Antonio Vivaldi | 199,086.0         |
| Jimmy Page      | 474,888.3         |
| Carlos Santana  | 499,234.3         |
| ...             | ...               |

GROUP BY और ORDER BY

SELECT Composer, AVG(Milliseconds) AS Average
FROM Track
GROUP BY Composer
ORDER BY Average

| Composer        | Average           |
|-----------------|-------------------|
| Pearl Jam       | 94,197.0          |
| Antonio Vivaldi | 199,086.0         |
| Jimmy Page      | 474,888.3         |
| Carlos Santana  | 499,234.3         |
| ...             | ...               |

दिशानिर्देश

SELECT स्टेटमेंट में कोई भी कॉलम या एक्सप्रेशन जो aggregate function नहीं है, उसे GROUP BY क्लॉज़ में होना चाहिए

SELECT Composer, AVG(Milliseconds), UnitPrice
FROM Track
GROUP BY Composer

column "track.unitprice" must appear in the GROUP BY clause or be used in an aggregate function
LINE 2: SELECT Composer, AVG(Milliseconds), UnitPrice

दिशानिर्देश

SELECT लिस्ट में कोई भी कॉलम या एक्सप्रेशन जो aggregate function नहीं है, उसे GROUP BY क्लॉज़ में होना चाहिए

SELECT Composer, AVG(Milliseconds), MAX(UnitPrice)
FROM Track
GROUP BY Composer

| Composer        | AVG(Milliseconds) | MAX(UnitPrice) |
|-----------------|-------------------|----------------|
| Antonio Vivaldi | 199,086.0         | 0.99           |
| Pearl Jam       | 94,197.0          | 0.99           |
| Jimmy Page      | 474,888.3         | 0.99           |
| Carlos Santana  | 499,234.3         | 0.99           |
| ...             | ...               | ...            |

दिशानिर्देश

GROUP BY में दिए गए एक्सप्रेशन को SELECT स्टेटमेंट में शामिल होना जरूरी नहीं है

SELECT AVG(Milliseconds)
FROM Track
GROUP BY Composer

| AVG(Milliseconds) |
|-------------------|
| 199,086.0         |
| 94,197.0          |
| 474,888.3         |
| 499,234.3         |
| ...               |

एकाधिक कॉलम

SELECT Country, City, COUNT(CustomerId)
FROM Customer
GROUP BY Country, City

| Country   | City                | COUNT(CustomerId) |
|-----------|---------------------|-------------------|
| Argentina | Buenos Aires        | 1                 |
| Australia | Sidney              | 1                 |
| Austria   | Vienne              | 1                 |
| Belgium   | Brussels            | 1                 |
| Brazil    | Brasilia            | 1                 |
| Brazil    | São José dos Campos | 1                 |
| Brazil    | São Paulo           | 2                 |
| ...       | ...                 | ...               |

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

Oracle SQL परिचय