1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

MySQL

Discussion in 'Databases' started by Šatra, Mar 20, 2020.

  1. #1
    Hi,
    I need to count how many workers is employeed in each department.
    How to do that? Untitled3.png Untitled4.png
    SEMrush
     
    Šatra, Mar 20, 2020 IP
    SEMrush
  2. sarahk

    sarahk iTamer Staff

    Messages:
    26,241
    Likes Received:
    3,865
    Best Answers:
    108
    Trophy Points:
    665
    #2
    select departments.dept_name, count(employees.emp_no)
    from employees
    left join departments on employees.department = department.dept_no
    group by department.dept_no
    Code (markup):
     
    sarahk, Mar 20, 2020 IP
  3. Šatra

    Šatra Peon

    Messages:
    18
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    1
    #3
    Column departments is not in table employees. It is in table v_full_employees. I need to get first column of all departments and second of number workers employeed in certain department.

    select departments.dept_name, count(v_full_employees.emp_no)
    from departments
    left join v_full_employees on v_full_employees.department = departments.dept_no
    group by departments.dept_no

    This code is correct. I think. Thanks.
     
    Last edited: Mar 20, 2020
    Šatra, Mar 20, 2020 IP
  4. Šatra

    Šatra Peon

    Messages:
    18
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    1
    #4
    How to count how many each department have employeed from each title? Maybe it is neede more tables?
    Untitled4.png Untitled9.png Untitled10.png
     
    Šatra, Mar 27, 2020 IP
  5. sarahk

    sarahk iTamer Staff

    Messages:
    26,241
    Likes Received:
    3,865
    Best Answers:
    108
    Trophy Points:
    665
    #5
    you just need count() and group by
     
    sarahk, Mar 27, 2020 IP
  6. Šatra

    Šatra Peon

    Messages:
    18
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    1
    #6
    This code shows only that titles which count is not 0. I need column of all titles. What do I need to fix? Untitled12.png
     
    Šatra, Mar 27, 2020 IP
  7. sarahk

    sarahk iTamer Staff

    Messages:
    26,241
    Likes Received:
    3,865
    Best Answers:
    108
    Trophy Points:
    665
    #7
    Your where clause requires that view7 has a value, so you'll never get all the titles with that query.
    What are you trying to find out with the "Customer Service" bit?
     
    sarahk, Mar 27, 2020 IP
  8. Šatra

    Šatra Peon

    Messages:
    18
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    1
    #8
    I want to count how many employeed into any department (for example: Custome Service) have title Senior Engineer,Staff,...
     
    Šatra, Mar 27, 2020 IP
  9. sarahk

    sarahk iTamer Staff

    Messages:
    26,241
    Likes Received:
    3,865
    Best Answers:
    108
    Trophy Points:
    665
    #9
    try
    select alltitles.title, count(view7.id)
    from alltitles left join view7 on alltitles.title = view7.title
    group by alltitles.title
    order by 2 DESC
    Code (markup):
     
    sarahk, Mar 27, 2020 IP
  10. Šatra

    Šatra Peon

    Messages:
    18
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    1
    #10
    In second row just need to continue : 'and view7.department="Customer Service"' and I think code is correct. Thanks
     
    Last edited: Mar 27, 2020
    Šatra, Mar 27, 2020 IP
  11. sarahk

    sarahk iTamer Staff

    Messages:
    26,241
    Likes Received:
    3,865
    Best Answers:
    108
    Trophy Points:
    665
    #11
    do you want "any department" or not?
     
    sarahk, Mar 27, 2020 IP
  12. Šatra

    Šatra Peon

    Messages:
    18
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    1
    #12
    OK. Maybe I did not write well. For each department individually.
     
    Šatra, Mar 28, 2020 IP
  13. sarahk

    sarahk iTamer Staff

    Messages:
    26,241
    Likes Received:
    3,865
    Best Answers:
    108
    Trophy Points:
    665
    #13
    try
    select view7.department, alltitles.title, count(view7.id) as countup
    from alltitles 
    left join view7 on alltitles.title = view7.title
    group by view7.department, alltitles.title
    order by 2 DESC
    Code (markup):
     
    sarahk, Mar 28, 2020 IP
  14. Šatra

    Šatra Peon

    Messages:
    18
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    1
    #14
    Last your code was enough.
     
    Šatra, Mar 29, 2020 IP