difficulties with COUNT function

user_18700465 0
Viewed 27

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;
sql
3 Answers

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

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

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
user_162807760
Related