If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Data Access, Manipulation & Batch Languages > ANSI SQL > SQL Query

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #2 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
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
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Dec 2008
Location: At work...
Posts: 84
Another solution:

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


Core SQL-99 compliant.
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Dec 2008
Location: At work...
Posts: 84
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")
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: May 2012
Posts: 155
Hallo JarlH and r937,

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

Regards,

Ratna
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Feb 2008
Location: Japan
Posts: 3,417
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.
Reply With Quote
  #7 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
doesn't the subquery in the FROM clause (derived table) require its own table alias?
__________________
rudy.ca | @rudydotca
Buy my SitePoint book: Simply SQL
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On