Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Location
    Dhaka,Bangladesh
    Posts
    86

    Unanswered: Informix Query Problem

    Dear Informix Experts,

    I am very new in Informix database. Follwoing SQL is giving syntex error. Can any one please guide me where i made mistake?? Its urgent please help.


    Select Distinct(Select count(status) From mmo where status = 1) As 'MMO Msg per Status 1',
    (Select count(status) From mmo where status = 2) As 'MMO Msg per Status 2',
    (Select count(status) From mmo where status = 3) As 'MMO Msg per Status 3',
    (Select count(status) From mmo where status = 4) As 'MMO Msg per Status 4',
    (Select count(status) From mmo where status = 5) As 'MMO Msg per Status 5',
    (Select count(status) From mmo where status = 6) As 'MMO Msg per Status 6',
    (Select count(status) From mmo where status = 7) As 'MMO Msg per Status 7',
    (Select count(status) From mmo where status = 8) As 'MMO Msg per Status 8',
    (Select count(status) From mmo where status = 9) As 'MMO Msg per Status 9',
    (Select count(status) From mmo where status = 10) As 'MMO Msg per Status 10',
    (Select count(status) From mmo where status = 11) As 'MMO Msg per Status 11',
    (Select count(status) From mmo where status = 12) As 'MMO Msg per Status 12',
    (Select count(status) From mmo where status = 13) As 'MMO Msg per Status 13',
    (Select count(status) From mmo where status = 14) As 'MMO Msg per Status 14',
    (Select count(status) From mmo where status = 15) As 'MMO Msg per Status 15',
    (Select count(status) From mmo where status = 16) As 'MMO Msg per Status 16',
    (Select count(status) From mmo where status = 17) As 'MMO Msg per Status 17',
    (Select count(status) From mmo where status = 18) As 'MMO Msg per Status 18'from mmo Order by 1
    hello

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    replace what you have there with this --
    Code:
    SELECT status
         , COUNT(*) AS status_count
      FROM mmo 
     WHERE status BETWEEN 1 AND 18
    GROUP
        BY status
    ORDER
        BY status
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2003
    Location
    Dhaka,Bangladesh
    Posts
    86
    Dear r937 ,

    As you replied

    SELECT status
    , COUNT(*) AS status_count
    FROM mmo
    WHERE status BETWEEN 1 AND 18
    GROUP
    BY status
    ORDER
    BY status

    but i made the query to export record in such format with column name as:

    MMO Msg per Status 1 MMO Msg per Status 2.........MMO Msg per Status 18
    0 2 15

    What i need to do? Please help
    hello

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Mahfuz
    What i need to do?
    use a front-end language like php or asp to re-arrange the query results
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    248
    Hi, although quite unefficient the real problem with:
    Select Distinct(Select count(status) From mmo where status = 1) As 'MMO Msg per Status 1',
    (Select count(status) From mmo where status = 2) As 'MMO Msg per Status 2'
    from mmo Order by 1
    is that the columntag can't be a regular string. If you use
    Code:
    AS MMO_Msg_per_Status_x
    as tags it will work. The columnheaders will be presented all lowercase though.

    Regards,
    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
  •