Informix Query Problem

Mahfuz · #1 (**permalink**) 08-01-09, 11:59

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

r937 · #2 (**permalink**) 08-01-09, 17:36

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

Mahfuz · #3 (**permalink**) 08-02-09, 01:33

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

r937 · #4 (**permalink**) 08-02-09, 04:15

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

Tyveleyn · #5 (**permalink**) 08-02-09, 04:56

Hi, although quite unefficient the real problem with:

Quote:

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: