count function

[jemz]

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

how do i use union all in this query?

Activity

  
  ACTNO       EMP_PERMITNO
     6                10000000000
     6                10000000000
     6                10000000000
     6                10000000000
     5                10000000010

EMPREFERENCE

  
 EMPNO              EMP_PERMITNO
     005                10000000000
     006                20000000000
     007                10000000000
     008                10000000000

EMPLOYEEDETAIL

    EMPDETNO        EMP_PERMITNO
     111                10000000000
     112                20000000000
     113                10000000000
     114                10000000000

Here is my code

 
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'

[StarLion]

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?

[guido2004]

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

[r937]

like everybody else, i have no idea what you're trying to do

however, note that when you do this --

SELECT COUNT(*) AS number FROM ...
UNION ALL
SELECT COUNT(*) AS number FROM ...

then you will get a result set that looks like this --

number
------
     9
    37

and you will have no way to discern which number came from which subquery

[jemz]

like everybody else, i have no idea what you're trying to do

however, note that when you do this --

SELECT COUNT(*) AS number FROM ...
UNION ALL
SELECT COUNT(*) AS number FROM ...

then you will get a result set that looks like this --

number
------
     9
    37

and you will have no way to discern which number came from which subquery

Hi r937,Okay let me change my problem.,


Suppose i have this 4 tables.how do i count and group them by empgrp_area where the empNO equals to 00001 ?

Thank you in advance and I am hoping for your positive response.

empheader

refNo         empNO
01              00001     
02              00002
03              00001
04              00001
05              00001
06              00003
07              00004
08              00001

empdetail

refNO        empitmcode                
01            0000000001                        
01            0000000002                      
01            0000000003
02            0000000004
02            0000000004
04            0000000006
05            0000000002
05            0000000002
03            0000000007
03            0000000006
08            0000000001

emptblitm

empitmcode                   empdesc                           empgrp_area       
0000000001                   puncher                            01A  
0000000002                   pencil                               01A
0000000003                   paper                               02A
0000000004                   chair                                02A 
0000000005                   table                                02A
0000000006                   computer                          03A
0000000007                   keyboard                          03A

empgrp

empgrp_area                  grp_desc
01A                              class1                            
02A                              class2
03A                              class3
04A                              class4  
05A                              class5

[jemz]

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.

Thank you.

[r937]

Suppose i have this 4 tables.how do i count and group them by empgrp_area where the empNO equals to 00001 ?

do you want separate counts from each table, or do you want to join the tables and count the joined rows?

[jemz]

Hi r937, Thank you for the reply.

or do you want to join the tables and count the joined rows?

yes i want to join.

[r937]

yes i want to join.

do you know how to join?

[jemz]

just little idea only.

[jemz]

I am not good in joining the table

[r937]

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

[jemz]

Hi, Thank you for giving example what is the output for this?

is this possible to display something like this

class1
class3
class4

base from the given above.

[r937]

Hi, Thank you for giving example what is the output for this?

what did you get when you tested it?

[jemz]

Hi, I haven't tested yet in that table,but i tried here in my table but i got 23 count
and my grp_area only 3,

Okay how to do like this

do you want separate counts from each table

[r937]

*sigh*

i don't think i understand where you're going

good luck, though

[jemz]

Hi, r937.i think i got it i just follow your idea and i get the exact output.