calculations using mysql

[Slowie]

hey guys ive been trying to do this one all night and i dont know if im either approching it wrong or its just not possible. i have tried multiple ways of doing it but havent succeded.

 

basically i have 2 tables one called user level and one called stafflist

 

now in the user level table i have 2 companies with 3 userlevels for each so

 

company

userlevel

1

1

1

2

1

3

2

1

2

2

2

3

now in the stafflist table there are lets say 40 staff members and each have a field filled in which is the company with either 1 or 2

they also each have a user level assigned. so 1 , 2 or 3

 

what i want is for the query to count how many staff are assigned each level for that company so the end result is a table like

 

company

userlevel

number of staff

1

1

15

1

2

3

1

3

2

2

1

18

2

2

2

2

3

0

 

could someone explain how i would go about this. ill figure out the exact code but i just need a shove in the right direction

[Slowie]

can no one give me a little advice on this?

[PFMaBiSmAd]

A) You would join the tables using the company columns

 

B) Use GROUP BY company,userlevel to consolidate the rows into the groups you want in the result set

 

C) Use COUNT() to add up the rows in each group to give the number of staff per group.

[Slowie]

ok so going on that my current code is

 

 

select options.company, options.user_level, options.branch, userlevel.Description,  branch.branch as branch from options 
  left join branch on options.branch=branch.branchid 
  left join userlevel on options.user_level=userlevel.level 
  where options.user_level != '0' And options.user_level != '2' And options.user_level != '4' AND options.company='".$_SESSION['company']."'

 

so the code after the changes will be

 

SELECT options.company, options.user_level, options.branch, Count(stafflist.userlevel) AS userlevel userlevel.Description,  branch.branch AS branch 
FROM options 
LEFT JOIN branch on options.branch=branch.branchid 
LEFT JOIN userlevel on options.user_level=userlevel.level 
LEFT JOIN stafflist on stafflist.branch=options.branch
WHERE options.user_level != '0' 
AND options.user_level != '2' 
AND options.user_level != '4' 
AND options.company='".$_SESSION['company']."'
GROUP BY options.branch, stafflist.userlevel

 

would this be correct?

[Slowie]

nope this is way out haha just been messing around with it again. i cant get this right

[fenway]

Show us the current output, and what you think is wrong.

[Slowie]

currently with

 

$sql = "select options.company, options.user_level, options.branch, userlevel.Description,  branch.branch as branch from options 
  left join branch on options.branch=branch.branchid 
  left join userlevel on options.user_level=userlevel.level 
  where options.user_level != '0' And options.user_level != '2' And options.user_level != '4' AND options.company='".$_SESSION['company']."'
  " ; 

 

i get the branch along with the userlevel

 

so that is

 

1 - 1

1 - 2

1 - 3

2 - 1

2 - 2

2 - 3

 

now i know i need to left join in the stafflist table and do a count similar to this one

 

SELECT branch, user_level, COUNT(*) AS staff
FROM stafflist
GROUP BY branch, user_level

 

this gives me the results i want but i need this in one mysql query for a table i just cant work out how to do it

 

[Slowie]

i solved it in the end the query i was looking for was as below. but thank you for the help everyone

 

SELECT options.optionsid, userlevel.Description, branch.branch, COUNT(*) AS Staff, stafflist.user_level
FROM stafflist
Left Join userlevel on stafflist.user_level=userlevel.level
Left Join branch On stafflist.branch=branch.branchid
Left Join options On userlevel.level=options.user_level Where options.branch=stafflist.branch
GROUP BY branch, user_level