डेटा एग्रीगेशन
SQL Server परिचय
John MacKintosh
Instructor
SUM - एक कॉलम
किसी कॉलम का कुल जोड़ SUM() से निकालें
SELECT
SUM(affected_customers) AS total_affected
FROM grid;
+----------------+
| total_affected |
|----------------|
| 70143996 |
+----------------+
SUM - दो या अधिक कॉलम
SELECT
SUM(affected_customers) AS total_affected
FROM grid;
SELECT
SUM (affected_customers) AS total_affected,
SUM (demand_loss_mw) AS total_loss
FROM grid;
+----------------+------------+
| total_affected | total_loss |
|----------------+------------|
| 70143996 | 177888 |
+----------------+------------+
गलत तरीका...
SELECT
SUM (affected_customers) AS total_affected,
(demand_loss_mw) AS total_loss
FROM grid;
Msg 8120, Level 16, State 1, Line 6
Column 'grid_demand_loss_mw' is invalid in the select list because
it is not contained in either an aggregate function or the GROUP BY clause.
उपनाम (aliases) का उपयोग करें
SELECT
SUM (affected_customers),
SUM (demand_loss_mw)
FROM grid;
+------------------+------------------+
| (No column name) | (No column name) |
|------------------+------------------|
| 70143996 | 177888 |
+------------------+------------------+
SELECT
SUM (affected_customers) AS total_affected,
SUM (demand_loss_mw) AS total_loss
FROM grid;
+----------------+------------+
| total_affected | total_loss |
|----------------+------------|
| 70143996 | 177888 |
+----------------+------------+
COUNT
SELECT
COUNT(affected_customers) AS count_affected
FROM grid;
+----------------+
| count_affected |
|----------------|
| 807 |
+----------------+
COUNT Distinct
SELECT
COUNT(DISTINCT affected_customers) AS unique_count_affected
FROM grid;
+-----------------------+
| unique_count_affected |
|-----------------------|
| 280 |
+-----------------------+
MIN
SELECT
MIN(affected_customers) AS min_affected_customers
FROM grid;
+------------------------+
| min_affected_customers |
|------------------------|
| 0 |
+------------------------+
SELECT
MIN(affected_customers) AS min_affected_customers
FROM grid
WHERE affected_customers > 0;
+------------------------+
| min_affected_customers |
|------------------------|
| 1 |
+------------------------+
MAX
SELECT
MAX(affected_customers) AS max_affected_customers
FROM grid;
+------------------------+
| max_affected_customers |
|------------------------|
| 4645572 |
+------------------------+
औसत
SELECT
AVG(affected_customers) AS avg_affected_customers
FROM grid;
+------------------------+
| avg_affected_customers |
|------------------------|
| 86919 |
+------------------------+
अभ्यास करते हैं!
SQL Server परिचय
