Database mein data ko sirf store karna kafi nahi hota, aksar hamein us data ka hisab-kitab (Calculations) karna padta hai aur unhe alag-alag hisson mein baantna (Grouping) padta hai. Inhe SQL mein Aggregate Functions aur Grouping Clauses kehte hain.
Chaliye inko HTML, PHP backend code, aur SQL syntax ke sath asan Hindi mein samajhte hain.
Maan lijiye hamare paas ek Orders table hai jisme columns hain: id, product_name, category, price, aur order_date.
1. COUNT, SUM, aur AVG (Calculations Ke Liye)
COUNT: Pata karta hai ki total kitne rows ya items hain.
SUM: Kisi numeric column (jaise price) ka total jod (total amount) nikalta hai.
AVG: Average (ausat) nikalta hai.
HTML aur PHP Code:
In sabhi calculations ko hum ek hi baar mein dashboard par statistics ke roop mein dikha sakte hain.
<?php
include("dbconnection.php");
// 1. COUNT - Total kitne orders hue?
$q1 = mysqli_query($con, "SELECT COUNT(id) AS total_orders FROM Orders");
$r1 = mysqli_fetch_array($q1);
// 2. SUM - Total kitni kamai (revenue) hui?
$q2 = mysqli_query($con, "SELECT SUM(price) AS total_revenue FROM Orders");
$r2 = mysqli_fetch_array($q2);
// 3. AVG - Ek customer average kitne ka samaan kharidta hai?
$q3 = mysqli_query($con, "SELECT AVG(price) AS avg_order_value FROM Orders");
$r3 = mysqli_fetch_array($q3);
?>
<div class="dashboard-cards">
<div class="card">Total Orders: <h3><?php echo $r1['total_orders']; ?></h3></div>
<div class="card">Total Revenue: <h3>₹<?php echo $r2['total_revenue']; ?></h3></div>
<div class="card">Average Ticket Size: <h3>₹<?php echo round($r3['avg_order_value'], 2); ?></h3></div>
</div>
2. GROUP BY (Category Ke Hisab Se Data Group Karna)
GROUP BY ka kaam hai ek jaisa data rakhne wali rows ko ek jhund (group) mein ikattha karna. Jaise, agar aap dekhna chahte hain ki har ek category mein total kitne paise kamaye gaye, toh hum category ke hisab se data group karenge.
HTML aur PHP Code:
Iska use aksar reports ya tables banane mein hota hai.
<table border="1">
<tr>
<th>Category</th>
<th>Total Items Sold</th>
<th>Total Sales (SUM)</th>
</tr>
<?php
include("dbconnection.php");
// Query: Category ke hisab se group karo, aur har group ka count aur sum nikalo
$query = "SELECT category, COUNT(id) AS total_qty, SUM(price) AS total_sales
FROM Orders
GROUP BY category";
$result = mysqli_query($con, $query);
while($row = mysqli_fetch_array($result)) {
echo "<tr>";
echo "<td>" . $row['category'] . "</td>";
echo "<td>" . $row['total_qty'] . "</td>";
echo "<td>" . $row['total_sales'] . "</td>";
echo "</tr>";
}
?>
</table>
3. HAVING (Grouped Data Par Filter Lagana)
Yahan aksar log galti karte hain: WHERE ka use normal rows ko filter karne ke liye hota hai, lekin GROUP BY hone ke baad jo groups bante hain, un par filter lagane ke liye HAVING ka use hota hai.
Maan lijiye aapko sirf wahi categories dikhani hain jinki total sales ₹5000 se zyada hui ho.
SQL Syntax aur Code:
// Query jisme HAVING ka use hua hai
$query = "SELECT category, SUM(price) AS total_sales
FROM Orders
GROUP BY category
HAVING SUM(price) > 5000";
Meaning: Yeh pehle saare data ko category ke hisab se group karega, unka total (SUM) nikalega, aur fir sirf unhi categories ko screen par dikhayega jinka total 5000 se upar hoga.
Ek Simple Farq: WHERE vs HAVING
| Feature | WHERE | HAVING |
| Kab chalta hai? | Data group hone se pehle | Data group hone ke baad |
| Kiske sath use hota hai? | Kisi bhi normal column par filter lagane ke liye | SUM, COUNT, AVG jaise functions ke result par filter lagane ke liye |
| Example | WHERE price > 500 | HAVING SUM(price) > 5000 |
0 Comments: