SQL Query

ratnalein · #1 (**permalink**) 01-07-13, 05:38

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

r937 · #2 (**permalink**) 01-07-13, 07:23

Code:

SELECT DISTINCT 
       school
     , ( SELECT COUNT(*)
           FROM daTable
          WHERE school <> t.school ) AS number
  FROM daTable AS t

JarlH · #3 (**permalink**) 01-07-13, 08:46

Another solution:

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

Core SQL-99 compliant.

JarlH · #4 (**permalink**) 01-07-13, 10:54

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")

ratnalein · #5 (**permalink**) 01-08-13, 04:09

Hallo JarlH and r937,

thank you guys, it really helped me to go further

. Thanks..

Regards,

Ratna

tonkuma · #6 (**permalink**) 01-08-13, 12:13

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.

r937 · #7 (**permalink**) 01-08-13, 14:58

doesn't the subquery in the FROM clause (derived table) require its own table alias?

Internet Services

Web Development

Digital Marketing

Consumer Tech