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):

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

I have tried the below but no luck:

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

CodePudding user response：

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

CodePudding user response：

SELECT C.attrition, C.businesstravel,COUNT(*)CNTT
FROM YOUR_TABLE   C
GROUP BY C.attrition, C.businesstravel

CodePudding user response：

You need to group by attrition and businesstravel because count of these unique group is required. Hope below query helps:-

select attrition,businesstravel,count(attrition)  from biz
   group by attrition,businesstravel