SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    Knowledge is key 2 progression Tryst's Avatar
    Join Date
    Sep 2003
    Location
    Wales
    Posts
    1,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Accumilating data from 2 columns to show as one result

    Hi all,

    I have a table that contains two columns (tohosp, and fromhosp) and would like to accumilate the values stored in both columns and output the result as one. So for my problem I would like to know the total amount of journeys that were made by a particular hospital.

    For example, the table looks like this...

    ------------------------------------
    Table Name: Journeys
    ------------------------------------
    id - tohosp - fromhosp
    ------------------------------------
    01 - KIDD - WCHU
    02 - WCHU - KIDD
    03 - KIDD - NEWL
    04 - NEWL - WCHU
    etc etc
    ------------------------------------

    Now the values in the tohosp and fromhosp columns are foreign keys and will link to a table called hospitals where I will group all hospitals and display their total number of journeys made (which is calculated through the tohosp and fromhosp columns).

    Hope you understand my requirements.

    (I am using MSSQL)

    Tryst

  2. #2
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    696
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    You mean like

    Code:
    select hospitalName,count(*) as numberOfJourneys from 
    (select tohosp as hospital from Journeys
     union all
    selecy fromhosp from Journeys) dt 
    inner join hosiptals on dt.hospital = hospitals.hospital
    group by hospitalName
    I've made some guesses about the column names in the hospital table.

  3. #3
    Knowledge is key 2 progression Tryst's Avatar
    Join Date
    Sep 2003
    Location
    Wales
    Posts
    1,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah, that what I wanted.

    I already knew that too

    Thanks

    Tryst


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
  •