pivot a group data summary

Question

I have the following table in sql and I need to use data summary

DEPARMENT	JOB	QUARTER	ITEM
Training	null	Q1	8
Support	null	Q2	4
Support	null	Q3	2
Research and Development	null	Q4	2
Support	Account Coordinator	Q1	8
Training	Account Coordinator	Q2	5
Engineering	Account Coordinator	Q3	1
Support	Account Coordinator	Q4	2
Services	Account Executive	Q1	5
Support	Account Executive	Q2	4
Support	Account Executive	Q3	5
Support	Account Executive	Q4	7
Human Resources	Account Representative I	Q1	1
Services	Account Representative III	N/I	1
Research and Development	Account Representative III	Q1	2
Business Development	Account Representative III	Q2	1
Support	Account Representative III	Q4	2
Training	Account Representative IV	Q1	4
Support	Account Representative IV	Q2	3
Support	Account Representative IV	Q4	5
Training	Accountant I	Q1	6
Product Management	Accountant I	Q2	1
Marketing	Accountant I	Q3	5

I have tried to use but it does not bring me the desired results

SELECT DEPARMENT
    , MAX(JOB)
    , COUNT(QUARTER)
FROM TESTHIRED_EMPLOYEE
GROUP BY DEPARMENT
    , JOB
    , QUARTER

To obtain a table like this

DEPARMENT	JOB	Q1	Q2	Q3	Q4
Support	Account Coordinator	30	0	10	5
Services	Account Representative III	0	2	3	1
Research and Development	Account Representative III		0	0	12
Business Development	Account Representative III	10	0	0	2
Support	Account Representative III	23	2	1	6

score 0 · Answer 1 · answered Jan 03 '23 at 15:23

This documentation should explain a bit: https://learn.microsoft.com/en-us/azure/databricks/sql/language-manual/sql-ref-syntax-qry-select-pivot. It also has solutions without a pivot with the use of, in your case, count(qarter) filter(where quarter = 'Q1') as Q1

1 Answers1