Help with complicated query

alighouston

i need some to help me write
pick up data from one table then multiply it with other table then add all the results
i am new to mysql i was able to get the data out with this query

SELECT * FROM `empoly` a inner join hours b on a.employ_name = b.employ_name

the values that i am getting i want to multiply them the and sum the result
thankyou

leatherback

You really need to be clearer on what you need...

select sum(hours) as totalhours from employ as a inner join hours as b on a.employ_name = b.employ_name group by employ_name

possibly?

Also.. When joining tables: Use ID colums, not names.

alighouston

i am making a payrole system in one table there are employ name and there wage rate in the other table there are hours entered by date every employ has a different pay rate i just want to add up each employs hours for certain period multiply by there wage rate then sum all the employes wages and give me total.

alighouston

ok tried few things but still no luck new code

SELECT  sum(employ_hours) as hours FROM `hours` where employ_hours =1 as side1 group by employ_name,  

select employ_pay from empoly group by employ_name1 where employ_pay =1 as side2,
sum (side1*side2) as total

alighouston

i am trying to multiply values from 2 different columns then the result should be added and displayed values i am new to sql coding can some one help

SELECT  sum(employ_hours) as hours FROM `hours` where employ_hours =1 as side1 group by employ_name,  

select employ_pay from empoly group by employ_name1 where employ_pay =1 as side2,
sum (side1*side2) as total

thankyou.

bpat1434

SELECT SUM(s1.`employ_hours`) as `hours`, s2.`employ_pay` as `rate`, (`hours` * `rate`) AS `pay`
FROM `hours` s1
LEFT JOIN `employ` s2
  ON s1.`employ_id` = s2.`id`
WHERE s1.`employ_hours` = 1
  AND s2.`employ_pay` = 1
GROUP BY `employ_id` WITH ROLLUP

Now, that's if you have the table hours with a field employ_id which is the value in the table employ column id. Otherwise, there's no real way to "map" from one table to the other, and your data is useless since rate wouldn't be able to be associated to the employee.

The "WITH ROLLUP" will add an extra row with the sum off all of the data for that employee id. Not sure if it would work, but it's worth a shot.

I merged these as there's no reason to be having the same discussion in two different places 🙁 Please don't cross post.

alighouston

will give it a try tomorrow to tired atm i have employ_name as common in both tables to like it even did picked up data from the tables linked with left join just coud not multiply it then sum it thanks for the with rollup thing never try it