difficulties with COUNT function

I am new to SQL and am having trouble using the count function to determine how many ‘YES’ or ‘NO’ for each criteria – apologies, this may be a stupid question.

I have a table with data like this (there are more businesstravel categories):

attrition	businesstravel
Yes	Travel_Rarely
No	Travel_Frequently
Yes	Travel_Rarely
No	Travel_Frequently
No	Travel_Rarely

And I want to count how many ‘Yes’ and ‘No’s align to each of the businesstravel categories. For example, like the below:

attrition	businesstravel	count
Yes	Travel_Rarely	43
No	Travel_Rarely	65
Yes	Travel_Frequently	72
No	Travel_Frequently	5

I have tried the below but no luck:

• SELECT businesstravel, attrition
• COUNT(attrition)
• FROM wa_fn_usec_hr_employee_attrition_tsv
• GROUP BY businesstravel;

Answer

Your query is nearly right. You’re missing a comma after “attrition” and you need to add attrition in the group by.

create table 
wa_fn_usec_hr_employee_attrition_tsv(
attrition varchar(10),
businesstravel varchar(50));
insert into 
wa_fn_usec_hr_employee_attrition_tsv
values
('Yes','Travel_Rarely'),
('No','Travel_Frequently'),
('Yes','Travel_Rarely'),
('No','Travel_Frequently'),
('No','Travel_Rarely');

✓

✓

SELECT 
  businesstravel, 
  attrition,
  COUNT(*)
FROM wa_fn_usec_hr_employee_attrition_tsv
GROUP BY 
  businesstravel,
  attrition;

businesstravel    | attrition | COUNT(*)
:---------------- | :-------- | -------:
Travel_Rarely     | Yes       |        2
Travel_Frequently | No        |        2
Travel_Rarely     | No        |        1

db<>fiddle here