16. deloitte azure databricks interview questions |

[@sushantbhardwaj1354]

spark sql and dataframe solution .. df1 = df.groupBy("DeptName").agg(count(when(col("Gender")=='M',1)).alias("Male"),count(when(col("Gender")=='F',1)).alias("Female"),count('*').alias("Total")) df1.show() spark.sql(''' select DeptName,count(case when Gender = 'M' then 1 end ) as male, count(case when Gender = 'F' then 1 end ) as Female, count(*) as total_emp from dept group by DeptName ''').show()

[@Javi-gp5jw]

Great video! Very informative and well-explained. Another solution I tried was : df.groupBy("DeptName") .agg(count('Gender').alias("TotalEmp"), count_if(df12.Gender=='M').alias("MaleEmp"), count_if(df12.Gender=='F').alias("FemaleEmp"), )

[@ssunitech6890]

Good, Keep Learning and please share videos to your friends

[@rawat7203]

Please continue this series, thanks

[@ssunitech6890]

Sure will record more videos on this playlist

[@kt-qg7uv]

Hello sir, I hope you are doing well. I am currently attending interviews I am facing difficulty to explain the SSIS projects in my Resume. Can you please help me with that. Please respond. Thank you

[@ssunitech6890]

Yes, sure Please find below videos for your project: kzbin.info/aero/PLNRxk1s77zfjbCU-BgnER0hrnf4TvY5A3

[@videodevloper277]

Nice

[@ssunitech6890]

Thanks, Please share to others

[@userbayya]

Pls keep code also in description

[@ssunitech6890]

Sure

[@ardsha]

this will also bring the result : df.groupBy('DeptName').agg(count(df.Gender).alias('TotalEmp'),sum(when (df.Gender=='M',1)).alias('Male_count'),sum(when(df.Gender=='F',1)).alias('FemaleCount')).show()

[@ssunitech6890]

Thanks Keep learning and growing 💗

[@datasculptor2895]

As per your query, In case of HR the male count will be shown as null, but should be shown as 0. So , we need to add otherwise(0) condition as well to get 0 count.