Given a transactions table with columns city, agent, and amount, which query returns total sales per city and counts distinct agents in each city?

hard📝 Application Q8 of 15

SQL - GROUP BY and HAVING

Given a transactions table with columns city, agent, and amount, which query returns total sales per city and counts distinct agents in each city?

ASELECT city, SUM(amount), COUNT(agent) FROM transactions GROUP BY city, agent;

BSELECT city, SUM(amount), COUNT(DISTINCT agent) FROM transactions GROUP BY city;

CSELECT city, SUM(amount), COUNT(agent) FROM transactions GROUP BY city;

DSELECT city, SUM(amount), COUNT(DISTINCT agent) FROM transactions;

Step-by-Step Solution

Solution:

Step 1: Group by city
We want totals per city, so GROUP BY city is correct.
Step 2: Aggregate functions
SUM(amount) calculates total sales; COUNT(DISTINCT agent) counts unique agents per city.
Final Answer:
SELECT city, SUM(amount), COUNT(DISTINCT agent) FROM transactions GROUP BY city; -> Option B
Quick Check:
COUNT(DISTINCT) counts unique values [OK]

Quick Trick: Use COUNT(DISTINCT) for unique counts [OK]

Common Mistakes:

MISTAKES

Grouping by both city and agent incorrectly
Using COUNT(agent) instead of COUNT(DISTINCT agent)
Omitting GROUP BY clause

Master "GROUP BY and HAVING" in SQL

9 interactive learning modes - each teaches the same concept differently

Learn Why Deep Visual Try Challenge Project Recall Time

Want More Practice?

15+ quiz questions · All difficulty levels · Free

Free Signup - Practice All Questions

More SQL Quizzes

Given a transactions table with columns city, agent, and amount, which query returns total sales per city and counts distinct agents in each city?

Step 1: Group by city

Step 2: Aggregate functions

Final Answer:

Quick Check:

Want More Practice?