CODEWITHSUNDEEP
sqlcount
Bauerhof
Bauerhof

Reputation: 165

How to get the total count of rows AND the count based on the distinct values of another column in the table

Suppose we have the table:

+----------+------------+
| order_id | order_type |
+----------+------------+
| 1        | classsic   |
+----------+------------+
| 2        | classic    |
+----------+------------+
| 3        | urgent     |
+----------+------------+
| 4        | urgent     |
+----------+------------+
| 5        | canceled   |
+----------+------------+
| 6        | canceled   |
+----------+------------+
| 7        | classic    |
+----------+------------+

and it is requested to get the total count of all orders as well as the count of orders for each order_type.

the returned result supposed to be similar to:

+-------+---------+--------+----------+
| total | classic | urgent | canceled |
+-------+---------+--------+----------+
| 7     | 3       | 2      | 2        |
+-------+---------+--------+----------+

which sql statement would get us the above result?

Upvotes: 1

Views: 60

Answers (3)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

Here is another way with Case expression if you don't wont go Dynamic or Pivot:

SELECT COUNT(*) total,
       SUM(CASE
               WHEN order_type = 'classic'
                    OR order_type = 'classsic'
               THEN 1
               ELSE 0
           END) classic,
       SUM(CASE
               WHEN order_type = ' urgent '
               THEN 1
               ELSE 0
           END) urgent,
       SUM(CASE
               WHEN order_type = 'canceled'
               THEN 1
               ELSE 0
           END) canceled
FROM <table_name>;

Result :

+-------+---------+--------+----------+
| total | classic | urgent | canceled |
+-------+---------+--------+----------+
| 7     | 3       | 2      | 2        |
+-------+---------+--------+----------+

Upvotes: 2

Valerica
Valerica

Reputation: 1630

This will do the desired output. More about PIVOT operator here

declare @tbl as table(
    order_id int
    ,order_type varchar(15)
)


insert into @tbl values (1, 'classic')
insert into @tbl values (2, 'classic')
insert into @tbl values (3, 'urgent')
insert into @tbl values (4, 'urgent')
insert into @tbl values (5, 'canceled')
insert into @tbl values (6, 'canceled')
insert into @tbl values (7, 'classic')


SELECT
    classic + urgent + canceled AS Total
    ,classic
    ,urgent
    ,canceled
FROM (
    SELECT order_id, order_type from @tbl   
) tbl
PIVOT(
    COUNT(order_id) FOR order_type IN (classic, urgent, canceled)
) pvt

Upvotes: 2

Radim Bača
Radim Bača

Reputation: 10701

use conditional aggregation

select count(*) total,
       count(case when order_type = 'classic' then 1 end) classic,           
       count(case when order_type = 'urgent ' then 1 end) urgent ,
       count(case when order_type = 'canceled ' then 1 end) canceled 
from your_table

Upvotes: 3

Related Questions