Hi, i am having problem of my query, I want to count how many records of employee having this permitno=‘10000000000’ and also in the empoyeedetail i want also to count and sum to the result of the first queried table
SELECT count(*)as number from
activity act inner join empreference ref
on act.emp_permitno = ref.emp_permitno
where act.emp_permitno = '10000000000'
union all
SELECT count(*)as number from employeedetail
where emp_permitno = '10000000000'
I’m… not sure why you want to do this? You mention a sum but dont have one? What numbers do you expect to get out of this query, given the input above?
Like StarLion I have no idea what you are trying to do, but if you want to sum the two counts in your query then try
SELECT SUM(number) as totalnumber
FROM (
SELECT count(*) AS number
FROM activity act
INNER JOIN empreference ref
ON act.emp_permitno = ref.emp_permitno
WHERE act.emp_permitno = '10000000000'
UNION ALL
SELECT count(*) AS number
FROM employeedetail
WHERE emp_permitno = '10000000000'
) AS a
Hi StarLion, I am referring on the result of the two count so if the count result 5 and the other count result 6 i want to sum both of them so i must be getting 11.
please take time to learn how to join from the following example:
SELECT empgrp.grp_desc
, COUNT(*)
FROM empheader
INNER
JOIN empdetail
ON empdetail.refNo = empheader.refNo
INNER
JOIN emptblitm
ON emptblitm.empitmcode = empdetail.empitmcode
INNER
JOIN empgrp
ON empgrp.empgrp_area = emptblitm.empgrp_area
WHERE empheader.empno = '00001'
GROUP
BY empgrp.grp_desc