Results 1 to 3 of 3

Thread: Union

  1. #1
    Join Date
    Mar 2004
    Posts
    3

    Post Unanswered: Union

    I was wondering if there is a way I could write this query as one query using union instead of 3 different queries?

    SELECT columna as column,count(ID) as Applications
    FROM tablea
    GROUP columna


    SELECT columna as column2, count(ID) as Approved
    FROM tablesa where substring(APP_DATE,1,6) >'200304' and in 'Approved')
    GROUP BY columna

    SELECT columna as column3, count(ID) as Booked, sum(AMT) as amt, sum(AMT)/count(ID) as lavg
    from tablea where substring(APP_DATE,1,6) >'200304' and STATUS in('book')
    group by columna

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    But there's got to be a better way than this

    Code:
       SELECT   columna   as column1
    	   ,count(ID) as Applications
    	   , Null As Col3 
    	   , Null As Col4
         FROM   tablea
     GROUP BY   columna 
    UNION ALL
       SELECT   columna as column2
    	   , count(ID) as Approved
    	   , Null As Col3 
    	   , Null As Col4
         FROM    tablesa 
        WHERE    substring(APP_DATE,1,6) >'200304' 
          AND    STATUS = 'Approved')
     GROUP BY    columna 
    UNION ALL
       SELECT    columna as column3
    	,    count(ID) as Booked
    	,    sum(AMT) as amt
    	,    sum(AMT)/count(ID) as lavg 
         FROM    tablea 
        WHERE    substring(APP_DATE,1,6) >'200304' 
          AND    STATUS in('book')
     GROUP BY columna
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Mar 2004
    Posts
    45
    SELECT ColumnA, COUNT(ID) Applications,
    SUM(CASE WHEN LEFT(AppDate,6) > '200304' AND Status = 'Approved' THEN 1 END) Approved,
    SUM(CASE WHEN LEFT(AppDate,6) > '200304' AND Status = 'Book' THEN 1 END) Booked,
    SUM(CASE WHEN LEFT(AppDate,6) > '200304' AND Status = 'Book' THEN Amt END)/
    SUM(CASE WHEN LEFT(AppDate,6) > '200304' AND Status = 'Book' THEN 1 END) LAvg
    FROM TableA
    GROUP BY ColumnA

    Why not use a temporal datatype for dates?
    Hans.

Posting Permissions

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