oracle SQL procedure error not ended properly

Question

I am trying to select the total number of each value in column paxtype with the following letters m,f,i,c but my error is sql not ended properly

(select b.PAXTYPE from xxxx b, xxx a)
(case b.PAXTYPE
    when 'M' then count('M')
    when 'F' then count('F')
    when 'I' then count('I')
    when 'C' then count('C')
    END)
where a.date_key=to_char(b.FLIGHTDATE,'RRRRMMDD')
and a.FLTNUM_KEY= trim(substr(b.flightnumber,3))
and a.origin=b.frm
and a.destination=b.too
--and a.date_key=20170801
--and fightnumber = '100'
and trim(a.cancelled) is null
and rownum = 1
)

actually in this code i want to get the total number of m,f,i,c in this column paxtype please help — Abel Yator, Sep 18 '17 at 09:20

score 0 · Accepted Answer · answered Sep 18 '17 at 09:22

Firstly You are not using the correct sql syntax. Also, there are several other problems with your query.

Table names should come after columns in select .
You need to use group by since you need count
There is no need for CASE block

why is ROWNUM = 1 required?

This query should work fine for your requirement.

SELECT b.PAXTYPE, COUNT (b.PAXTYPE)
          FROM xxxx b, xxx a
         WHERE     a.date_key = TO_CHAR (b.FLIGHTDATE, 'RRRRMMDD')
               AND a.FLTNUM_KEY = TRIM (SUBSTR (b.flightnumber, 3))
               AND a.origin = b.frm
               AND a.destination = b.too
               --and a.date_key=20170801
               --and fightnumber = '100'
               AND TRIM (a.cancelled) IS NULL
      --and rownum = 1  # Why was it required?
      GROUP BY b.PAXTYPE;

Additionally, If you need only the counts for M,F,I,C then add AND b.PAXTYPE IN ('M','F','I','C') before group by

oracle SQL procedure error not ended properly

1 Answers1