How to get the count from a specific column and display separately

Question

I would like to check how can I re-write the query so I can show the different stages in different columns

original query:

select order_id, stage from table

original result:

order_id  stage
111       1
111       1
222       1
111       2
111       3
111       4
222       2

the result i would like to see is based on the count of each stage:

order_id  stage_1 stage_2 stage_3 stage_4
111       2       1       1       1
222       1       1

sorry for the trouble and thanks in advance

Answer 1

Try this:

SELECT COUNT(column_name)
FROM table_name;

This will return the number of rows from one column.

For more Details: https://www.w3schools.com/sql/sql_count_avg_sum.asp

Answer 2

I figured this out already, thanks for the replies

select order_id,
count (case when stage = 'stage_1' then 1 else null end) as stage_1,
count (case when stage = 'stage_2' then 1 else null end) as stage_2,
count (case when stage = 'stage_3' then 1 else null end) as stage_3,
count (case when stage = 'stage_4' then 1 else null end) as stage_4

from table

group by order_id

Answer 3

If your SQL implementation doesn't provide PIVOT query, your approach is a common workaround for PIVOT-ing.

But in some other SQL implementation like BigQuery, you can query like below and get same result. Kindly check if your SQL supports PIVOT first.

DECLARE sample ARRAY<STRUCT<order_id INT64, stage INT64>> DEFAULT [
  (111, 1), (111, 1), (222, 1), (111, 2), (111, 3), (111, 4), (222, 2)
];

WITH dataset AS (
 SELECT * FROM UNNEST(sample)
)
SELECT * FROM dataset PIVOT( COUNT(stage) AS stage FOR stage IN (1, 2, 3, 4) );

output:
+----------+---------+---------+---------+---------+
| order_id | stage_1 | stage_2 | stage_3 | stage_4 |
+----------+---------+---------+---------+---------+
|      111 |       2 |       1 |       1 |       1 |
|      222 |       1 |       1 |       0 |       0 |
+----------+---------+---------+---------+---------+