Select case when len(col2) - len(replace(col2, ' , ', ' ')) = 2 then 3 when len(col2) - len(replace(col2, ' , ', ' ')) = 1 then 2 else 1 end as cnt from tbl_cnt; This method will work even if the length of the characters are more than 1.
That's the good question Shashank. In that scenario we can use string split function to split values. select col1, count(value) as cnt from tbl_cnt CROSS APPLY STRING_SPLIT(col2, ',') group by col1
Here you need to use windows functions lag and then do a time difference for processing time with cte as (select *,lag(timestamp,1,timestamp) over(partition by machine_id,process_id order by timestamp) as differ from machine_activity), cte2 as (select *, (timestamp-differ) as processing_time from cte) select machine_id, avg(processing_time) from cte2 where processing_time 0 group by machine_id
Nice explanation 👌 👍 👏
Select case when len(col2) - len(replace(col2, ' , ', ' ')) = 2 then 3 when len(col2) - len(replace(col2, ' , ', ' ')) = 1 then 2 else 1 end as cnt from tbl_cnt; This method will work even if the length of the characters are more than 1.
select col1,len(col3) as count from
(
select col1,col2,replace(col2,',','') as col3 from tbl_cnt
) as a
select col1, len(col2)-len(replace(col2,',','')) +1 as no of word
select col1, count(value)
from #tbl_cnt
cross apply string_split(col2,',')
group by col1
Select length(col2)-length(replace(col2,',')+1 as cnt from enput;
string_split also we can use
Yes Siddharth. We can use it
select id,
count(value)
from hobby
cross apply string_split(hobby_list,',')
group by id
what if there are 2 letters separated by comma?
for example,
insert into tbl_cnt values (1, 'ab,b,cd'),(2, 'ac,b')
That's the good question Shashank. In that scenario we can use string split function to split values.
select col1, count(value) as cnt
from tbl_cnt
CROSS APPLY STRING_SPLIT(col2, ',')
group by col1
@@CloudChallengers What if my col2 has null's data...is that query pass test in this scenario
Thanks for the explanation.
Can you please explain below problem which i am struggling to solve
Activity table:
+------------+------------+---------------+-----------+
| machine_id | process_id | activity_type | timestamp |
+------------+------------+---------------+-----------+
| 0 | 0 | start | 0.712 |
| 0 | 0 | end | 1.520 |
| 0 | 1 | start | 3.140 |
| 0 | 1 | end | 4.120 |
| 1 | 0 | start | 0.550 |
| 1 | 0 | end | 1.550 |
| 1 | 1 | start | 0.430 |
| 1 | 1 | end | 1.420 |
| 2 | 0 | start | 4.100 |
| 2 | 0 | end | 4.512 |
| 2 | 1 | start | 2.500 |
| 2 | 1 | end | 5.000 |
+------------+------------+---------------+-----------+
Output:
+------------+-----------------+
| machine_id | processing_time |
+------------+-----------------+
| 0 | 0.894 |
| 1 | 0.995 |
| 2 | 1.456 |
+------------+-----------------+
The resulting table should have the machine_id along with the average time as processing_time, which should be rounded to 3 decimal places.
Here you need to use windows functions lag and then do a time difference for processing time
with cte as (select *,lag(timestamp,1,timestamp) over(partition by machine_id,process_id order by timestamp) as differ from machine_activity),
cte2 as (select *, (timestamp-differ) as processing_time from cte)
select machine_id, avg(processing_time) from cte2 where processing_time 0 group by machine_id
Thank You Ram for the Solution
@@iamram436
@@velagambetipoojitha Thanks for posting the question, I hope it is cleared. Thanks @iamram436 for the solution.