एग्रीगेट arithmetic फंक्शन

SQL Server में डेटा मैनिपुलेट करने के लिए फ़ंक्शंस

Ana Voicu

Data Engineer

COUNT()

किसी समूह में मिली items की संख्या लौटाता है.

COUNT([ALL] expression)
COUNT(DISTINCT expression)
COUNT(*)

COUNT() उदाहरण

SELECT 
    COUNT(ALL country) AS total_countries,
    COUNT(country) AS total_countries,
    COUNT(DISTINCT country) AS distinct_countries,
    COUNT(*) AS all_voters
FROM voters;

| count_countries_all | count_countries | distinct_countries | all_voters |
|---------------------|-----------------|--------------------|------------|
| 196                 | 196             | 11                 | 196        |

SUM()

समूह से सभी मानों का योग लौटाता है.

SUM([ALL] expression)
SUM(DISTINCT expression)

SUM() उदाहरण

SELECT
    first_name,
    last_name,
    total_votes
FROM voters
WHERE total_votes = 153;

| first_name | last_name | total_votes |
|------------|-----------|-------------|
| Isabella   | Roberts   | 153         |
| Chase      | Ward      | 153         |
| Kendra     | Ortega    | 153         |
| Bruce      | Moreno    | 153         |

SELECT
    SUM(ALL total_votes) AS tot_votes1,
    SUM(total_votes) AS tot_votes2,
    SUM(DISTINCT total_votes) AS dist       
FROM voters
WHERE total_votes = 153;

| tot_votes1  | tot_votes2  | tot_dis_votes  |
|-------------|-------------|----------------|
| 612         | 612         | 153            |

MAX() और MIN()

MAX([ALL] expression) 
MAX(DISTINCT expression)

अभिव्यक्ति का अधिकतम मान लौटाता है.

MIN([ALL] expression) 
MIN(DISTINCT expression)

अभिव्यक्ति का न्यूनतम मान लौटाता है.

MAX() और MIN() उदाहरण

SELECT 
    MIN(rating) AS min_rating,
    MAX(rating) AS max_rating
FROM ratings;

| min_rating |max_rating |  
|------------|-----------|
| 1.0000     | 5.0000    |

AVG()

समूह के मानों का औसत लौटाता है.

AVG([ALL] expression) 
AVG(DISTINCT expression)

SELECT 
    AVG(rating) AS avg_rating,
    AVG(DISTINCT rating) AS avg_dist
FROM ratings;

| avg_rating |avg_dist |  
|------------|---------|
| 3.184665   | 2.788461|

डेटा को समूहित करना

SELECT company,
    AVG(rating) AS avg_rating
FROM ratings
GROUP BY company;

| company    |avg_rating |  
|------------|-----------|
| A. Morin   | 3.250000  |
| Acalli     | 3.500000  |
| Adi        | 3.000000  |...

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

SQL Server में डेटा मैनिपुलेट करने के लिए फ़ंक्शंस