My approach to solve this (Mysql) with cte as ( select m.value as velocity, n.value as level from (select * from auto_repair where indicator ="level") n inner join (select * from auto_repair where indicator ="velocity") m on n.client = m.client and n.auto =m.auto and n.repair_date = m.repair_date) select velocity, sum(if(level = "good",1,0))as "good", sum(if(level= "wrong",1,0)) as "wrong", sum(if(level= "regular",1,0)) as "wrong" from cte group by velocity order by velocity;

@easycommerceclasses8241

Ай бұрын

thanks

Great one as always Thoufik! Here's my approach. Thought I would use string manipulation and NTILE functions for a change ;) ===================================================== declare @partition int set @partition = (select COUNT(*) from auto_repair); with cte as (select NTILE(@partition/2) over(order by (select 1)) grp_num, * from auto_repair), cte2 as (select STRING_AGG(value, '-') value_agg from cte group by grp_num) select SUBSTRING(value_agg, CHARINDEX('-', value_agg, 1) + 1, 5) as [Velocity], COUNT(case when (LEFT(value_agg, CHARINDEX('-', value_agg,1) - 1)) = 'good' then 1 else null end) [good], COUNT(case when (LEFT(value_agg, CHARINDEX('-', value_agg,1) - 1)) = 'wrong' then 1 else null end) [wrong], COUNT(case when (LEFT(value_agg, CHARINDEX('-', value_agg,1) - 1)) = 'regular' then 1 else null end) [regular] from cte2 group by SUBSTRING(value_agg, CHARINDEX('-', value_agg, 1) + 1, 5) ======================================================

Many thanks Thoufiq! Using MSSQL.

Thanks for the question. This #30daysSQLquerychallenge is helping a lot. Here is my approach With velocity as (select row_number() over() as rn,value as velocity from auto_repair where indicator='velocity'), level as (select row_number() over() as rn,value as level from auto_repair where indicator='level') select v.velocity, count(case when level='good' then velocity end) as good, count(case when level='wrong' then velocity end) as wrong, count(case when level='regular' then velocity end) as regular from velocity v join level l on v.rn=l.rn group by 1 order by velocity;

@haleylearn

2 ай бұрын

Wow so interesting

Why i am addicted to this type of questions ?? i am watching all of sql video one by one and learning new things in every video

Hi sir, than you for all the hardwork you put in! This is an amazing series. :)) Below is my approach for this problem : with mycte as ( select new_velocity, case when new_velocity = x.value then (lag(x.value,1) over(partition by client, repair_date order by repair_date)) end as new_val from ( select *, case when indicator = 'velocity' then value else NULL end as new_velocity from auto_repair )as x ) select new_velocity as velocity, sum(case when new_val = 'good' then 1 else 0 end) as good, sum(case when new_val = 'wrong' then 1 else 0 end) as wrong, sum(case when new_val = 'regular' then 1 else 0 end) as regular from mycte where new_velocity is not null and new_val is not null group by new_velocity

@CebuProvince

4 ай бұрын

this is the correct solution, I done this at the same way

@user-mw4dv3nz5n

4 ай бұрын

with cte as ( select client ,repair_date , MAX(CASE WHEN indicator = 'level' then value END) as level , MAX(case when indicator = 'velocity' then value end) as velocity from auto_repair group by client ,repair_date ) select velocity , COUNT(CASE WHEN level = 'good' then 1 END) as 'Good' , COUNT(CASE WHEN level = 'wrong' then 1 END) as 'Wrong', COUNT(CASE WHEN level = 'regular' then 1 END) as 'Regular' from cte group by velocity you could have optimized it.

Thanks a lot , Sir

Thanks

Wow thank You!

Thank You so much

❤❤

Nice problem thanks for giving some tricky 😊questions to slove

@hrishikeshXXV

24 күн бұрын

slove

Thank you sir, I want to learn this kind of complex query from you. If you are providing any training then I am eager to join sir

with cte as ( select value as velocity ,lvl,count(*) as cnt from( select indicator,value,lag(value) over(order by client , auto,repair_date) as lvl from auto_repair )x where indicator ='velocity' group by 1,2 ) select velocity, sum(case when lvl='good' then cnt else 0 end) as good, sum(case when lvl='wrong' then cnt else 0 end) as wrong, sum(case when lvl='regular' then cnt else 0 end) as regular from cte group by 1

This is another approach for solving the problem with cte as( select sum(case when indicator = 'velocity' then value end) velocity, max(case when indicator = 'level' then value end) levels, repair_date,client,auto from auto_repair group by repair_date,client,auto) select velocity, case when sum(case when levels ='good' then 1 else 0 end) good, sum(case when levels = 'wrong' then 1 else 0 end) wrong, sum(case when levels = 'regular' then 1 else 0 end) regular from cte group by velocity order by velocity;

Here is my approach using mysql with cte as (SELECT client, auto, repair_date, indicator, value,row_number() over () as rn FROM auto_repair ) ,cte1 as (select client, auto, repair_date, indicator, value, rn,lag(value) over (order by rn) as cnt from cte ) select value as Velocity,sum(case when cnt='good' then 1 else 0 end) as good, sum(case when cnt='wrong' then 1 else 0 end) as wrong, sum(case when cnt='regular' then 1 else 0 end) as regular from cte1 where indicator='velocity' group by value order by Velocity

with a as(select *,lag(value) over(partition by client,auto,repair_date) as value2 from auto_repair) select value, sum(case when value2 = "good" then 1 end) as good, sum(case when value2 = "regular" then 1 end) as regular, sum(case when value2 = "wrong"then 1 end) as wrong from a where indicator = "velocity" group by 1;

My solution -MSSQL with cte as( select indicator,value,lag(value) over(partition by client, auto, repair_date order by (select null) ) val from auto_repair) select value, sum(iif(val= 'good',1,0)) good, sum(iif(val = 'wrong', 1,0)) wrong , sum(iif(val = 'regular',1, 0)) regular from cte where val is not null group by value;

using Oracle: select listagg(case indicator when 'velocity' then value else null end, '') as velocity, listagg(case indicator when 'level' then value else null end, '') as lev from auto_repair group by client, repair_date ) select * from cte pivot ( count(lev) for lev in ('good' good, 'wrong' wrong, 'regular' regular )) order by velocity;

Hello sir , In Joins , how to know, when to use 1 condition or when to use 3 conditions? Like here you joined level and Velocity Table by Self join auto=auto, client=client, repair date= repair date. How to figure out this when to use 3 columns for condition 6:50. Please reply 🙏

using the T-SQL function looks more better...

suing MySQL and multiple CTEs with cte as ( select indicator, value, lag(value,1) over(partition by client,auto,repair_date order by repair_date) as vel from auto_repair), cte1 as( select * from cte where vel is not null), cte2 as(select value, vel, count(*) as num from cte1 group by value,vel) select value , max(case when vel = 'good' then num else 0 end) as good, max(case when vel = 'regular' then num else 0 end) as regular, max(case when vel = 'wrong' then num else 0 end) as wrong from cte2 group by value

solved in mysql with cte as (select v.value as velocity, l.value as level from auto_repair l join auto_repair v on l.client=v.client and l.auto=v.auto and l.repair_date=v.repair_date where l.indicator='level' and v.indicator='velocity') select velocity, sum(case when level="good" then 1 else 0 end) as Good, sum(case when level="wrong" then 1 else 0 end) as Wrong, sum(case when level="Regular" then 1 else 0 end) as Regular from cte group by velocity order by velocity

with cte1 as ( select client, auto, repair_date, min(case when indicator = 'level' then value end) as indi, min(case when indicator = 'velocity' then value end) as velocity from auto_repair group by client, auto, repair_date ) select velocity, sum(case when indi = 'good' then 1 else 0 end) as good, sum(case when indi = 'wrong' then 1 else 0 end) as wrong, sum(case when indi = 'regular' then 1 else 0 end) as regular from cte1 group by velocity

with cte as ( select * from auto_repair pivot(max(value) for indicator in ('level', 'velocity')) as p(client, auto, repair_date, level, velocity) order by client, auto ), cte1 as ( select *, case when level = 'good' then 'good' else null end as good, case when level = 'wrong' then 'wrong' else null end as wrong, case when level = 'regular' then 'regular' else null end as regular from cte ) select velocity, count(good) as good, count(wrong) as wrong, count(regular) as regular from cte1 group by velocity order by velocity;

Solution for MySQL Workbench :- with cte as( select v.value as velocity, l.value as level, count(1) as value from auto_repair l join auto_repair v on l.client = v.client and l.auto = v.auto and l.repair_date = v.repair_date where l.indicator = 'level' and v.indicator = 'velocity' group by v.value, l.value order by v.value, l.value ) select velocity, sum(case when level = "good" then value else 0 end) as good, sum(case when level = "wrong" then value else 0 end) as wrong, sum(case when level = "regular" then value else 0 end) as regular from cte group by velocity;

;with cte as ( select *,row_number()over(order by (select null)) as rn from auto_repair),cte2 as ( select *, case when indicator='velocity' then lag(value)over(order by rn ) else null end as vel_val from cte ) select value as velocity, sum(case when vel_val='good' then 1 else 0 end ) as good, sum(case when vel_val='wrong' then 1 else 0 end ) as wrong, sum(case when vel_val='regular' then 1 else 0 end ) as regular from cte2 where vel_val is not null group by value

Here it's my approach in MSSQL... WITH LEVEL AS (SELECT * FROM AUTO_REPAIR WHERE INDICATOR='LEVEL'), VELOCITY AS (SELECT * FROM AUTO_REPAIR WHERE INDICATOR='VELOCITY'), JOINS AS(SELECT L.*,V.VALUE AS VELOCITY_VALUE FROM LEVEL L JOIN VELOCITY V ON L.CLIENT=V.CLIENT AND L.AUTO=V.AUTO AND L.REPAIR_DATE=V.REPAIR_DATE), CATEGORY AS ( SELECT *, CASE WHEN VALUE='GOOD' THEN 1 ELSE 0 END AS GOOD, CASE WHEN VALUE='REGULAR' THEN 1 ELSE 0 END AS REGULAR, CASE WHEN VALUE='WRONG' THEN 1 ELSE 0 END AS WRONG FROM [JOINS]) SELECT VELOCITY_VALUE,sum(GOOD),sum(REGULAR),sum(WRONG) FROM CATEGORY GROUP BY VELOCITY_VALUE

select velocity ,count(case level when 'good' then 1 end) good ,count(case level when 'wrong' then 1 end) wrong ,count(case level when 'regular' then 1 end) regular from ( select a.value velocity, b.value level from auto_repair a join (select * from auto_repair where indicator like 'level') b on a.client=b.client and a.auto=b.auto and a.repair_date = b.repair_date where a.indicator like 'velocity') t group by velocity

MySQL Solution - with cte as ( select v.value velocity, l.value level, count(v.value) as cnt from auto_repair v join auto_repair l on v.client = l.client and v.repair_date = l.repair_date and v.auto = l.auto where v.indicator = 'velocity' and l.indicator = 'level' group by v.value,l.value ) select velocity, sum(case when level = 'good' then cnt else 0 end ) as good, sum(case when level = 'wrong' then cnt else 0 end) as wrong, sum(case when level = 'regular' then cnt else 0 end ) as regular from cte group by velocity order by velocity

with cte as ( select t2.value as velocity,t1.value as level from auto_repair t1 join auto_repair t2 on t1.client=t2.client and t1.auto=t2.auto and t1.repair_date=t2.repair_date where t1.indicator='level' and t2.indicator='velocity') select velocity, sum(case when level='good' Then 1 else 0 end) as good, sum(case when level='wrong' Then 1 else 0 end) as wrong, sum(case when level='regular' Then 1 else 0 end) as regular from cte group by velocity

select velocity,sum(good)as good,sum(wrong)as wrong,sum(regular)as regular from (select ar.client,ar.auto,ar.repair_date, max(case when indicator='velocity' then value end) as velocity, sum(case when value='good' then 1 else 0 end) as good, sum(case when value='wrong' then 1 else 0 end) as wrong, sum(case when value='regular' then 1 else 0 end) as regular from auto_repair ar group by client,auto,repair_date)A group by velocity order by velocity;

we can avoid doing the join with the help of window function as this can improve the performance, my approach is below -----mysql approach with cte as ( select *, lag(value) over(order by client) as 'level' from auto_repair ), cte_final as ( select value as velocity, sum(case when level = 'good' then 1 else 0 end) as good, sum(case when level = 'wrong' then 1 else 0 end) as wrong, sum(case when level = 'regular' then 1 else 0 end) as regular from cte where indicator='velocity' group by 1 order by 1 ) select * from cte_final;

-- How to create second table from first table(image attached) client, auto, repair_date, indicator, value WITH CTE AS ( select V. value AS VELOCITY, L. value from auto_repair L JOIN auto_repair V ON L.client= V.client AND L. auto= V. auto AND L. repair_date= V.repair_date WHERE L.indicator= "LEVEL" AND V.indicator='velocity'), CTEA AS ( SELECT VELOCITY, value, count(value) AS REPTS FROM CTE group by VELOCITY, value order by VELOCITY, value) SELECT VELOCITY, MAX(CASE WHEN value= "wrong" THEN REPTS ELSE 0 END) AS wrong, MAX(CASE WHEN value= "good" THEN REPTS ELSE 0 END) AS good, MAX(CASE WHEN value= "regular" THEN REPTS ELSE 0 END) AS regular FROM CTEA GROUP BY VELOCITY;

-- SOLUTION1: Using GROUPBY, MAX(), SUM() SELECT velocity , SUM(CASE WHEN [level] = 'good' THEN 1 ELSE 0 END) AS 'good' , SUM(CASE WHEN [level] = 'wrong' THEN 1 ELSE 0 END ) AS 'wrong' , SUM(CASE WHEN [level] = 'regular' THEN 1 ELSE 0 END ) AS 'regular' FROM ( SELECT client, auto, repair_date , MAX(CASE WHEN indicator = 'level' THEN value END) AS level , MAX(CASE WHEN indicator = 'velocity' THEN value END) AS velocity FROM auto_repair GROUP BY client, auto, repair_date ) subquery GROUP BY velocity; -- SOLUTION 2: Using INNER JOIN and SUM(CASE WHEN), GROUPBY SELECT v.[value] , SUM(CASE WHEN l.[value] = 'good' THEN 1 ELSE 0 END) AS 'good' , SUM(CASE WHEN l.[value] = 'wrong' THEN 1 ELSE 0 END) AS 'wrong' , SUM(CASE WHEN l.[value] = 'regular' THEN 1 ELSE 0 END) AS 'regular' FROM auto_repair l JOIN auto_repair v ON l.client = v.client AND l.[auto] = v.[auto] AND l.repair_date = v.repair_date WHERE l.indicator = 'level' AND v.indicator = 'velocity' GROUP BY v.[value]; -- SOLUTION 3: Using LEAD(), ROW_NUMBER(), COUNT() SELECT value_lead , COUNT(CASE WHEN value = 'good' THEN 1 END) AS 'good' , COUNT(CASE WHEN value = 'wrong' THEN 1 END) AS 'wrong' , COUNT(CASE WHEN value = 'regular' THEN 1 END) AS 'regular' FROM ( SELECT * , LEAD(value) OVER(PARTITION BY client, auto, repair_date ORDER BY client) AS value_lead , ROW_NUMBER() OVER( PARTITION BY client, auto, repair_date ORDER BY client) AS rn FROM auto_repair ) table_x WHERE rn = 1 GROUP BY value_lead;

hello everyone, here i share my query which is same as thoufiq but only diffrence is in the second argument in the crosstab function, and i don't know why i am not getting same ans as him insted i am getting some erorr. if any one could assist me that would be greatfull. SELECT * FROM crosstab( 'SELECT v.value AS velocity, l.value AS level, COUNT(1) AS cnt FROM auto_repair l JOIN auto_repair v ON v.client = l.client AND l.auto = v.auto AND l.repair_date = v.repair_date WHERE l.indicator = ''level'' AND v.indicator = ''velocity'' GROUP BY v.value, l.value ORDER BY v.value' , ('good', 'regular', 'wrong') ) AS result(velocity VARCHAR, good BIGINT, regular BIGINT, wrong BIGINT);

-- MY APPROACH: ------------- 1. CREATE VIEW a table including 3 columns: Value; Level; COUNT(level) GROUP BY Value, Level CREATE VIEW cte AS ( SELECT value, lag, COUNT(*) FROM (SELECT *, LAG(value) OVER(PARTITION BY client, repair_date) FROM input_10) WHERE indicator = 'velocity' GROUP BY value, lag) ------------- 2. Use CROSSTAB to PIVOT TABLE SELECT * FROM crosstab ('SELECT * FROM cte', 'SELECT DISTINCT(lag) FROM cte ORDER BY lag') AS RESULT (velocity INT, good INT, regular INT, wrong INT)

Hi, in your download file is it a little bit different from your video presentaion, in the your file is the field "Count" listet, and this makes a wrong result. Can u check it please. The results a different as your online presentation. Ty. 😇 -- Solution using PIVOT in Micrososft SQLServer select * from ( select v.value velocity, l.value level,count(1) as count😇 from auto_repair l join auto_repair v on v.auto=l.auto and v.repair_date=l.repair_date and l.client=v.client where l.indicator='level' and v.indicator='velocity' group by v.value,l.value ) bq pivot ( count(level) for level in ([good],[wrong],[regular]) ) pq; velocity count good wrong regular 50 1 0 1 0 70 1 0 1 0 80 1 1 0 1 90 2 1 0 0

@kanikamittal7349

4 ай бұрын

You should use SUM(count) in the pivot query pq. It would give the correct result then. I have solved in similar way. It would be good if you change the name of 3 column in base query to something else since COUNT is a pre defined function

@CebuProvince

4 ай бұрын

Yes, Sum is the solution, look in the code of @saralavasudevan5167, she offer the correct solution of the task #11

with cte as ( select value, row_number() over() as count from auto_repair where indicator = 'velocity'), cte1 as (select value, row_number() over() as count from auto_repair where indicator = 'level') select c.value as velocity, count(case when c1.value = 'good' then c.value end) as 'good', count(case when c1.value = 'wrong' then c.value end) as 'wrong', count(case when c1.value = 'regular' then c.value end) as 'regular' from cte c join cte1 c1 on c.count = c1.count group by c.value order by c.value

@CebuProvince

4 ай бұрын

The function 'row_number' must have an OVER clause with ORDER BY.