Questions about SQL query and transform row and col

Question

I'm a bit new to SQL and would like to do some query on these tables. the schema looks like this:

tb1:

id	store_id	sold_count
abc	store1	30
def	store1	20
ghi	store2	50

tb2:

id	error_id	error_type
jkl	error1	error_type_A
mno	error2	error_type_A
pqr	error3	error_type_B
stu	error4	error_type_B
vwx	error5	error_type_B

tb3:

tb1_id	tb2_id
abc	jkl
abc	mno
def	pqr
ghi	stu
ghi	vwx

I want to do a query and get a table like this:

Stats	Error_Type_A	Error_Type_B
Raw_Count	2	3
Percentage	0.4	0.6
Error_Rate	0.02	0.03

Raw_Count: count of A/B error in total (tb2)
Percentage: percent of type A/B error (tb2)
Error_Rate: count of type A/B error (tb2) / sum of sold_count (tb1)

Now my thought is to do this in 2 steps, first to get a cte table like this, and then transform column and row.

Error_Type	Raw_Count	Percentage	Error_Rate
Error_Type_A	2	0.4	0.02
Error_Type_B	3	0.6	0.03

Here is the sql I wrote, but seems not working:

WITH cte (Error_Type, Raw_Count, Percentage, Error_Rate)
AS (
select error_type,
       count(error_type),
       (count(error_type) * 1.0 / (select count(*) From tb2)),
       (count(error_type) * 1.0 / (select sum(sold_count) from tb1)) 
from tb2
group by product_type
)
select Stats, [error_type_A], [error_type_B]
from
(
  select Error_Type, Percentage, Error_Rate
  from cte
  unpivot
  (
    value for States in (Raw_Count, Percentage, Error_Rate)
  ) unpiv
) src
pivot
(
  sum(value)
  for Error_Type in ([error_type_A], [error_type_B])
) piv

Any idea on how I can solve this? Really need some help, thanks a lot!!!

The Impaler · Accepted Answer · 2021-04-15 02:07:32Z

You can do:

with
t as (
  select c.error_type, count(*) as cnt
  from tb1 a
  join tb3 b on b.tb1_id = a.id
  join tb2 c on c.id = b.tb2_id
  group by c.error_type
),
s as (
  select
    (select cnt from t where error_type = 'error_type_A') as a,
    (select cnt from t where error_type = 'error_type_B') as b,
    (select sum(cnt) from t) as total
)
select 'Raw_Count' as stats, a as error_type_a, b as error_type_b from s
union all
select 'Percentage', 1.0 * a / total, 1.0 * b / total from s
union all
select 'Error_Rate', 1.0 * a / (select sum(sold_count) from tb1), 
  1.0 * b / (select sum(sold_count) from tb1) from s

Result:

 stats       error_type_a            error_type_b           
 ----------- ----------------------- ---------------------- 
 Raw_Count   2                       3                      
 Percentage  0.40000000000000000000  0.60000000000000000000 
 Error_Rate  0.02000000000000000000  0.03000000000000000000

See running example at DB Fiddle.

Collectives™ on Stack Overflow

Questions about SQL query and transform row and col

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related