5 Replies Latest reply: Dec 10, 2012 5:47 AM by 603257 RSS

    Case with Count

    user12303461
      Dear all,
      We got some SQL state to generate the report which is counting the container from different forwarder:

      select 'AE001',count(*) from cargo
      where forwarder = 'ARG'
      and container = 'AE001'
      and index <> '1'
      union all
      select 'BB001',count(*) from cargo
      where forwarder = 'ARG'
      and container = 'BB001'
      and index <> '1'
      union all
      select 'DR001',count(*) from cargo
      where forwarder = 'ARG'
      and container = 'DR001'
      and index <> '1'
      union all
      select 'FK001',count(*) from cargo
      where forwarder = 'ARG'
      and container = 'FK001'
      and index <> '1'
      union all
      select 'SQ001',count(*) from cargo
      where forwarder = 'ARG'
      and container = 'SQ001'
      and index <> '1'

      And we will get a result as below

      EMCM01     COUNT(*)
      AE001     63
      BB001     44
      DR001     2
      FK001     8
      SQ001     12

      However you may aware there are so many forwarders.
      I am looking to reformat the statement to get the result like:

      FWD     AE001     BB001     DR001     FK001     SQ001
      ARG     63     44     2     8     12
      BRZ     23     67     8     14     23
      CHN     2     9     1     23     1
      .
      .
      .


      Any help will be very apprecaite!!

      Thanks and Regards,
      Donald
        • 1. Re: Case with Count
          Ashu_Neo
          You can use PIVOT. For more check the link. It's a common a thread for this kind of query.

          https://forums.oracle.com/forums/thread.jspa?messageID=9360005&tstart=0

          Thanks!
          • 2. Re: Case with Count
            AlbertoFaenza
            Hi,

            welcome to the forum.

            Please read SQL and PL/SQL FAQ

            Additionally when you put some code or output please enclose it between two lines starting with {noformat}
            {noformat}
            
            i.e.:
            {noformat}
            {noformat}
            SELECT ...
            {noformat}
            {noformat}
            
            It will be easier to read.
            
            Ashu is right. In the FAQ you can find several examples. In your case it is important your Oracle version as PIVOT is available from 11g only. In that case there are other ways to get the same result.
            
            Please don't forget when you post your questions to post always CREATE TABLE and INSERT statements with sample data. Or at least WITH statement to have data to reproduce your case.
            
            Regards.
            Al                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
            • 3. Re: Case with Count
              jeneesh
              user12303461 wrote:
              And we will get a result as below

              EMCM01     COUNT(*)
              AE001     63
              BB001     44
              DR001     2
              FK001     8
              SQ001     12
              To get this result, you dont need UNION. Just GROUP BY
              select container,count(*) cnt
              from cargo
              where forwarder = 'ARG'
              and index != '1'
              and container in ('AE001','BB001','DR001','FK001','SQ001')
              group by container
              However you may aware there are so many forwarders.
              I am looking to reformat the statement to get the result like:

              FWD     AE001     BB001     DR001     FK001     SQ001
              ARG     63     44     2     8     12
              BRZ     23     67     8     14     23
              CHN     2     9     1     23     1
              .
              .
              .
              Use PIVOT as already mentioned using the above query.
              It will be something like below.
              BUT, are you actually having a column nmaed "INDEX" ? Didnt you get any error during table creation?
              with reqd_data as
              (
                select forwarder,container
                from cargo
                where index != '1'
                --and forwarder = 'ARG'
                --and container in ('AE001','BB001','DR001','FK001','SQ001') 
              )
              select *
              from reqd_data
              pivot
                ( count(*) for container in ('AE001','BB001','DR001','FK001','SQ001'))
              Edited by: jeneesh on Dec 10, 2012 2:54 PM
              • 4. Re: Case with Count
                Lokanath Giri
                Jinesh query should server your purpose
                If you need columns without any single quotes, just modify Jinesh's query as follows
                With container
                AS
                ( 
                SELECT 'AE001'  container, 'ARG' forwarder FROM dual
                UNION ALL
                SELECT 'BB001'  container, 'ARG' forwarder FROM dual
                UNION ALL
                SELECT 'BB001'  container, 'ARG' forwarder FROM dual
                UNION ALL
                SELECT 'BB001'  container, 'ARG' forwarder FROM dual
                UNION ALL
                SELECT 'BB001'  container, 'BRZ' forwarder FROM dual
                UNION ALL
                SELECT 'BB001'  container, 'ARG' forwarder FROM dual
                UNION ALL
                SELECT 'BB001'  container, 'CNN' forwarder FROM dual
                UNION ALL
                SELECT 'BB001'  container, 'CNN' forwarder FROM dual
                UNION ALL
                SELECT 'BB001'  container, 'ARG' forwarder FROM dual  
                )
                select *
                 from container
                 pivot
                  ( count(*) for container in ('AE001' AS AE001,'BB001' AS BB001,'DR001' AS DR001,'FK001' AS FK001,'SQ001' AS SQ001))
                /
                
                  FOR      AE001      BB001      DR001      FK001      SQ001
                --- ---------- ---------- ---------- ---------- ----------
                CNN          0          2          0          0          0
                BRZ          0          1          0          0          0
                ARG          1          5          0          0          0
                • 5. Re: Case with Count
                  603257
                  You may also want to investigate using XML output from the PIVOT command because if you add any more forwarders then you have to change the code to refect that. Oracle cannot dynamically create columns in a query.