SitePoint Sponsor

User Tag List

Results 1 to 17 of 17

Thread: count function

  1. #1
    SitePoint Wizard
    Join Date
    May 2012
    Posts
    1,109
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Post count function

    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
    Code:
      
      ACTNO       EMP_PERMITNO
         6                10000000000
         6                10000000000
         6                10000000000
         6                10000000000
         5                10000000010
    EMPREFERENCE
    Code:
      
     EMPNO              EMP_PERMITNO
         005                10000000000
         006                20000000000
         007                10000000000
         008                10000000000

    EMPLOYEEDETAIL
    Code:
        EMPDETNO        EMP_PERMITNO
         111                10000000000
         112                20000000000
         113                10000000000
         114                10000000000

    Here is my code

    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'

  2. #2
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    72 Post(s)
    Tagged
    0 Thread(s)
    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?
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  3. #3
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,501
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    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
    Code:
    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

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    like everybody else, i have no idea what you're trying to do

    however, note that when you do this --
    Code:
    SELECT COUNT(*) AS number FROM ...
    UNION ALL
    SELECT COUNT(*) AS number FROM ...
    then you will get a result set that looks like this --
    Code:
    number
    ------
         9
        37
    and you will have no way to discern which number came from which subquery
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard
    Join Date
    May 2012
    Posts
    1,109
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    like everybody else, i have no idea what you're trying to do

    however, note that when you do this --
    Code:
    SELECT COUNT(*) AS number FROM ...
    UNION ALL
    SELECT COUNT(*) AS number FROM ...
    then you will get a result set that looks like this --
    Code:
    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
    Code:
    refNo         empNO
    01              00001     
    02              00002
    03              00001
    04              00001
    05              00001
    06              00003
    07              00004
    08              00001
    empdetail
    Code:
    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
    Code:
    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
    Code:
    empgrp_area                  grp_desc
    01A                              class1                            
    02A                              class2
    03A                              class3
    04A                              class4  
    05A                              class5

  6. #6
    SitePoint Wizard
    Join Date
    May 2012
    Posts
    1,109
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by jemz View Post
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Wizard
    Join Date
    May 2012
    Posts
    1,109
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by jemz View Post
    yes i want to join.
    do you know how to join?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Wizard
    Join Date
    May 2012
    Posts
    1,109
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    just little idea only.

  11. #11
    SitePoint Wizard
    Join Date
    May 2012
    Posts
    1,109
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I am not good in joining the table

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    please take time to learn how to join from the following example:
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Wizard
    Join Date
    May 2012
    Posts
    1,109
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by jemz View Post
    Hi, Thank you for giving example what is the output for this?
    what did you get when you tested it?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Wizard
    Join Date
    May 2012
    Posts
    1,109
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    *sigh*

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

    good luck, though
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Wizard
    Join Date
    May 2012
    Posts
    1,109
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi, r937.i think i got it i just follow your idea and i get the exact output.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •