Results 1 to 7 of 7

Thread: SQL Query

  1. #1
    Join Date
    May 2012
    Posts
    155

    SQL Query

    Hallo everyone,

    I am facing a simple and at the same time complicated SQL query problem. I have a table as follow:

    Code:
    School            Name           donation_amount   
    -------------------------------------------------
    School_01         Ratna          20                  
    School_01         Ratna          30
    School_01         Lena           45
    School_02         Ratna          55
    School_02         Bob            10
    School_03         Peter          5
    I would like to list all the schools. To each school, it shall have the number of donation processes from OTHER schools:

    Code:
    Schule         Number
    -----------------------
    School_01      3
    School_02      4
    School_03      5
    For example, for School_02, it results 4 as the number of the donation processes(3 donation processes from School_01 and 1 from School_03)

    How can I produce the SQL for this?
    I was thinking, for this problem, I would have to do a GROUP BY on School, in order to list the Schools. And then it continues with the aggregate function. I am stucking here. The aggregate function is not so easy, because it refers to the other schools (For example, for School_01, the aggregate function is implemented for School_02 and School_03, not for School_01 as normally implemented)

    I was thinking to perhaps use an OLAP function. But untill now, I havent found the right one to solve this problem.


    Thanks for your help.

    Regards,

    Ratna

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT DISTINCT 
           school
         , ( SELECT COUNT(*)
               FROM daTable
              WHERE school <> t.school ) AS number
      FROM daTable AS t
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2008
    Location
    At work...
    Posts
    87
    Another solution:

    select school, (select count(*) from t) - count(*) as number
    from t
    group by school


    Core SQL-99 compliant.

  4. #4
    Join Date
    Dec 2008
    Location
    At work...
    Posts
    87
    Testes some other alternatives, this one was the fastest:
    select school, (select count(*) from t) - cnt as number
    from (select school, count(*) as cnt from t group by school)


    (The following feature outside Core SQL-99 is used: F591, "Derived tables")

  5. #5
    Join Date
    May 2012
    Posts
    155
    Hallo JarlH and r937,

    thank you guys, it really helped me to go further. Thanks..

    Regards,

    Ratna

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,470
    This was the least cost query within some queries tested on DB2 on Windows.

    Mimer SQL Developers - Mimer SQL-2003 Validator
    Mimer SQL-2003 Validator

    Check your SQL against the SQL-2003 standard using the Mimer SQL Validator 2003.

    The SQL-2003 Validator!

    Enter your SQL statement(s) in the box below and simply click the "Test SQL" button:

    Code:
    SELECT school
         , total_cnt - cnt AS number
     FROM (SELECT school
                , COUNT(*)               AS cnt
                , SUM( COUNT(*) ) OVER() AS total_cnt
            FROM  t
            GROUP BY
                  school
          )
    ;
    Code:
    Result:
    
    The following features outside Core SQL-2003 are used:
    
    T611, "Elementary OLAP operations"
    F591, "Derived tables"
    
    The following vendor reserved word is used:
    
    NUMBER

    DB2 Express-C 9.7.5 on Windows/XP.
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     t
    ( School , Name , donation_amount ) AS (
    VALUES
      ( 'School_01' , 'Ratna' , 20 )
    , ( 'School_01' , 'Ratna' , 30 )
    , ( 'School_01' , 'Lena'  , 45 )
    , ( 'School_02' , 'Ratna' , 55 )
    , ( 'School_02' , 'Bob'   , 10 )
    , ( 'School_03' , 'Peter' ,  5 )
    )
    SELECT school
         , total_cnt - cnt AS number
     FROM (SELECT school
                , COUNT(*)               AS cnt
                , SUM( COUNT(*) ) OVER() AS total_cnt
            FROM  t
            GROUP BY
                  school
          )
    ;
    ------------------------------------------------------------------------------
    
    SCHOOL    NUMBER     
    --------- -----------
    School_01           3
    School_02           4
    School_03           5
    
      3 record(s) selected.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    doesn't the subquery in the FROM clause (derived table) require its own table alias?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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