W3. SQL Sum

Question 1

Q: What does the SQL SUM() function do?

Answer 1

A: It returns the total sum of a numeric column.

Question 2

Q: Write a query to find the total quantity in the “Quantity” column of the “OrderDetails” table.

Answer 2

SELECT SUM(Quantity)
FROM OrderDetails;

Question 3

Q: What is the syntax for using SUM() with a condition?

Answer 3

SELECT SUM(column_name)
FROM table_name
WHERE condition;

Question 4

Q: How can you use the WHERE clause with SUM()?

Answer 4

A: Add a WHERE clause to specify which rows to include in the sum calculation, e.g., WHERE ProductID = 11.

Question 5

Q: Write a query to return the sum of “Quantity” for records with ProductID = 11.

Answer 5

SELECT SUM(Quantity) 
FROM OrderDetails 
WHERE ProductID = 11;

Question 6

Q: How can you give a descriptive name to the result column of SUM()?

Answer 6

A: Use the AS keyword to set an alias, e.g., AS total.

Question 7

Q: Write a query to find the total quantity and label it as “total.”

Answer 7

SELECT SUM(Quantity) AS total 
FROM OrderDetails;

Question 8

Q: How do you use SUM() with GROUP BY?

Answer 8

A: Combine SUM() with GROUP BY to get the sum for each group, e.g., GROUP BY OrderID.

Question 9

Q: Write a query to return the total quantity for each OrderID in “OrderDetails” and label it “Total Quantity.”

Answer 9

SELECT OrderID, SUM(Quantity) AS [Total Quantity] 
FROM OrderDetails 
GROUP BY OrderID;

Question 10

Q: Can you use an expression inside the SUM() function?

Answer 10

A: Yes, e.g., SUM(Quantity * 10) to multiply each quantity by 10 before summing.

Question 11

Q: Write a query that multiplies each quantity by 10 and then sums it in the “OrderDetails” table.

Answer 11

SELECT SUM(Quantity * 10) 
FROM OrderDetails;

Question 12

Q: How can you join tables to calculate a sum with related data?

Answer 12

A: Use a JOIN to combine tables, then apply SUM() to calculate based on data from both tables.

Question 13

Q: Write a query to find the total earnings by joining “OrderDetails” and “Products” to use actual prices.

Answer 13

SELECT SUM(Price * Quantity) 
FROM OrderDetails 
LEFT JOIN Products ON OrderDetails.ProductID = Products.ProductID;