LFCS · Question #119
Which of the following SQL queries counts the number of occurrences for each value of the field order_type in the table orders?
The correct answer is B. SELECT order_type,COUNT(*) FROM orders GROUP BY order_type. To count the occurrences of each distinct value in a column, an SQL query uses GROUP BY on the column and COUNT(*) in the SELECT clause.
Question
Options
- ASELECT order_type,COUNT(*) FROM orders WHERE order_type=order_type;
- BSELECT order_type,COUNT(*) FROM orders GROUP BY order_type;
- CCOUNT(SELECT order_type FROM orders);
- DSELECT COUNT(*) FROM orders ORDER BY order_type;
- ESELECT AUTO_COUNT FROM orders COUNT order_type;
How the community answered
(42 responses)- B88% (37)
- C2% (1)
- D5% (2)
- E5% (2)
Why each option
To count the occurrences of each distinct value in a column, an SQL query uses `GROUP BY` on the column and `COUNT(*)` in the `SELECT` clause.
The `WHERE order_type=order_type` clause is always true for any row where `order_type` is not NULL and does not perform grouping or counting of distinct occurrences.
This query correctly uses the `GROUP BY order_type` clause to segment the `orders` table into groups based on unique values in the `order_type` column. The `COUNT(*)` function then counts all rows within each of these groups, effectively providing the number of occurrences for each distinct `order_type`.
`COUNT()` is an aggregate function that takes a column or expression, not an entire `SELECT` statement, as its argument in this context.
`ORDER BY order_type` sorts the results but does not group and count occurrences; `SELECT COUNT(*)` without `GROUP BY` would only return the total number of rows in the table.
`AUTO_COUNT` and `COUNT order_type` are not valid SQL syntax for counting grouped occurrences.
Concept tested: SQL `GROUP BY` and `COUNT()` aggregate function
Source: https://www.w3schools.com/sql/sql_groupby.asp
Topics
Community Discussion
No community discussion yet for this question.