#1
  1. I love your chinese eyes :*
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Jan 2006
    Location
    Her heart... she claims!
    Posts
    1,703
    Rep Power
    1052

    receive second SELECT of UNION ALL as different column names


    Hi

    I have a table ORDERS. i want to count

    1 - orders for which invoices are issued
    2 - orders for which invoices are not issued

    I tried to use UNION ALL (as OUTER UNION is not supported in MySQL)

    but it combines the second SELECT columns under the first SELECT column name, even though, I gave second select columns different names.

    my query is as follow,

    Code:
    SELECT  
                     (SELECT COUNT(orderID) FROM orders WHERE invoiceStatus=1) as allInvoices, 
                     count(o.orderID) as totalInvoices,
                     o.orderDate, c.companyName from orders o, customers c 
                     where o.invoiceStatus=1 AND o.customerID=c.customerID 
                            group by o.customerID
    
    UNION ALL
    
    SELECT   
                       (SELECT COUNT(orderID) FROM orders WHERE invoiceStatus=0) as Unissued, 
                       count(o.orderID) as totalUnissued,o.orderDate, c.companyName from 
                       orders o, customers c where o.invoiceStatus=0 AND o.customerID=c.customerID 
                       group by o.customerID
    how can I get the SECOND SELECT columns horizontally as different columns (as OUTER UNION does)?

    thank you
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,664
    Rep Power
    4288
    i do not understand why you want it that way

    surely there is ~some~ work that could or should be done in the front-end language (e.g. php)

    i recommend against doing it with sql, but hey, you want it, you got it
    Code:
    SELECT customerID
         , companyName 
         , MAX(allInvoices)               AS allInvoices
         , MAX(totalInvoices)             AS totalInvoices
         , MAX(latest_invoiced_orderdate) AS latest_invoiced_orderdate
         , MAX(Unissued)                  AS Unissued
         , MAX(totalUnissued)             AS totalUnissued
         , MAX(latest_unissued_orderdate) AS latest_unissued_orderdate
      FROM ( SELECT c.customerID
                  , c.companyName 
                  , ( SELECT COUNT(*) 
                        FROM orders 
                       WHERE invoiceStatus = 1 ) AS allInvoices
                  , COUNT(*)                     AS totalInvoices
                  , MAX(o.orderDate)             AS latest_invoiced_orderdate
                  , NULL                         AS Unissued
                  , NULL                         AS totalUnissued
                  , NULL                         AS latest_unissued_orderdate
               FROM customers c 
             INNER
               JOIN orders o
                 ON o.customerID = c.customerID 
                AND o.invoiceStatus = 1 
             GROUP 
                 BY c.customerID         
             UNION ALL         
             SELECT c.customerID
                  , c.companyName 
                  , NULL                         AS allInvoices
                  , NULL                         AS totalInvoices
                  , NULL                         AS latest_invoiced_orderdate
                  , ( SELECT COUNT(*) 
                        FROM orders 
                       WHERE invoiceStatus = 0 ) AS Unissued
                  , COUNT(*)                     AS totalUnissued
                  , MAX(o.orderDate)             AS latest_unissued_orderdate
               FROM customers c 
             INNER
               JOIN orders o
                 ON o.customerID = c.customerID 
                AND o.invoiceStatus = 0 
             GROUP 
                 BY c.customerID
           ) AS u
    GROUP
        BY customerID

    Comments on this post

    • Will-O-The-Wisp agrees : Thanks for helping!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. I love your chinese eyes :*
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Jan 2006
    Location
    Her heart... she claims!
    Posts
    1,703
    Rep Power
    1052
    want to drop a ton of rep but my dropdown shows only 0

    anyway, before reading your reply, I already found that i was overdoing it on database query level. So i wrote some flexible class functions to deal with the situation.

    thank you very much. I learn a lot from you

IMN logo majestic logo threadwatch logo seochat tools logo