Which of the following SQL queries counts the number of occurrences for each value of the field order_type in the table orders?

Which of the following SQL queries counts the number of occurrences for each value of the field order_type in the table orders?
A . SELECT order_type,COUNT(*) FROM orders WHERE order_type=order_type;
B . SELECT order_type,COUNT(*) FROM orders GROUP BY order_type;
C . COUNT(SELECT order_type FROM orders);
D . SELECT COUNT(*) FROM orders ORDER BY order_type;
E . SELECT AUTO_COUNT FROM orders COUNT order_type;

View Answer

Answer: B

Explanation:

The correct SQL query to count the number of occurrences for each value of the field order_type in the table orders is:

SELECT order_type,COUNT(*) FROM orders GROUP BY order_type;

This query uses the SELECT statement to retrieve the values of the order_type field and

the COUNT(*) function to count the number of rows for each order_type. The GROUP BY clause groups the rows by the order_type field, so that the count is calculated for each distinct value of order_type. The result of this query is a table with two columns: order_type and count, where each row shows the number of orders for a specific order_type. The other options are incorrect for the following reasons:

A: This query uses a WHERE clause that is always true, since order_type=order_type for every row. Therefore, this query returns the same result as SELECT order_type,COUNT(*) FROM orders;, which is a table with one row that shows the total number of orders, regardless of the order_type.

C: This query is syntactically invalid, since the COUNT function cannot take a subquery as an argument. The correct way to use a subquery with COUNT is COUNT((SELECT order_type FROM orders));, which returns the total number of orders, regardless of the order_type.

D: This query uses the ORDER BY clause to sort the rows by the order_type field, but it does not group them by order_type. Therefore, this query returns the same result as SELECT COUNT(*) FROM orders;, which is a table with one row that shows the total number of orders, regardless of the order_type.

E: This query is syntactically invalid, since there is no such function as AUTO_COUNT in SQL, and

the COUNT function cannot take a field name as an argument. The correct way to use COUNT with a

field name is COUNT(order_type);, which returns the number of non-null values in the order_type

field.

Reference: [SQL COUNT Function]

[SQL GROUP BY Statement]

[SQL SELECT Statement]