SitePoint Sponsor

User Tag List

Results 1 to 2 of 2

Thread: record counting

  1. #1
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    record counting

    Code:
    myTable1
    
    (id)
     1
     2
     3
     4
     5
    
    myTable1
    
    (in) (out)
    
     2    1
     3    1
     4    1
     5    2
    I have myTables like the above.

    When a variables is 1, the query below produces the result below.

    Code:
    code
    
    SELECT id
    FROM myTable1 mm 
    WHERE mm.id<>1
    AND mm.id IN (SELECT myTable2.in FROM myTable2 WHERE myTable2.out=1)
    
    result
    
    2
    3
    4
    I like to know the number of the result above in SQL.
    The trial code1 below seems not to work.
    Code:
    trial code1
    SELECT count(*) as count
    FROM myTable1 mm 
    WHERE mm.id<>1
    AND mm.id IN (SELECT myTable2.in FROM myTable2 WHERE myTable2.out=1)
    
    target result
    
    3
    If I should use GROUP BY for getting COUNT, how can I use GROUP BY in this case?


    The trial code2 and the trial code3 below are all failed.

    Code:
    trial code2
    SELECT count(*) as count
    FROM myTable1 mm 
    WHERE mm.id<>1
    AND mm.id IN (SELECT myTable2.in FROM myTable2 WHERE myTable2.out=1)
    GROUP BY myTable2.out
    Code:
    trial code3
    SELECT count(*) as count
    FROM myTable1 mm 
    WHERE mm.id<>1
    AND mm.id IN (SELECT myTable2.in FROM myTable2 WHERE myTable2.out=1)
    GROUP BY myTable2.in

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,491
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by dotJoon View Post
    The trial code1 below seems not to work.
    Care to explain what 'seems not to work' mean? Do you get an error? Do you get a result different from 3? If so, what result do you get?


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
  •