Results 1 to 7 of 7

Thread: Count Question

  1. #1
    Join Date
    Jan 2010
    Posts
    3

    Question Unanswered: Count Question

    Good Evening,

    This is an awesome collection of information in these forums. I have the query below that returns data as follows:

    Now it returns:
    cpe_mkt Model Count
    Atlanta mod1 4
    Atlanta mod2 7
    Charlotte mod1 2
    Charlotte mod2 1

    Is there any way to make the results only show one set of cpe_mkt in the rows, then show a column with the count for each model type?

    Would like it to show:

    cpe_mkt mod1 mod2
    Atlanta 4 7
    Charlotte 2 1


    here is my current query.


    Select CPE_MKT, MODEL , count(*)
    FROM CPE.CPE_INVENTORY
    LEFT JOIN CPE.CPE_MANUFACTUERS
    ON CPE_INVENTORY.MANUFACTURE_ID=CPE_MANUFACTUERS.MANU FACTURE_ID
    LEFT JOIN CPE.CPE_MODEL
    ON CPE_INVENTORY.MODEL_ID=CPE_MODEL.CPE_MODEL_ID
    LEFT JOIN CPE.CPE_LOCATIONS
    ON CPE_INVENTORY.CPELOCATION_ID=CPE_LOCATIONS.CPE_LOC ATION_ID
    WHERE CPE.CPE_INVENTORY.CPESTATUS_ID = (4) -- status id 4 = inservice
    and MANUFACTURE_NAME = 'CISCO'
    and CPE_INVENTORY.LASTMODIFIEDDTS between to_date ( '12/07/2009', 'MM/DD/YYYY') and to_date ( '12/15/2009', 'MM/DD/YYYY')
    and CPE.CPE_INVENTORY.LASTMODIFIEDBY not like 'ntwscan_EXT_UPD'
    group by CPE_MKT, MODEL
    ORDER BY MODEL,CPE_MKT


    Thank you for any help you can provide

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by MRLancaster View Post
    Is there any way to make the results only show one set of cpe_mkt in the rows, then show a column with the count for each model type?
    yes, but it depends on how many different models there are

    a couple of comments...

    you wrote LEFT JOINs but they are executed as inner joins because of your WHERE conditions

    there is no such thing as the TO_DATE function in ANSI SQL (you're prolly thinking of oracle)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2010
    Posts
    3

    Models

    There are about 8 different models that I need to report on. The DB has about 100 altogether.

    Should I change those to inner joins?

    This is an Oracle DB. Should I move this to the Oracle thread?

    Thank you for the reply.
    Mike

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT iv.cpe_mkt
         , COUNT(CASE WHEN mo.model = mod1
                      THEN 1 ELSE NULL END) AS count_mod1
         , COUNT(CASE WHEN mo.model = mod2
                      THEN 2 ELSE NULL END) AS count_mod2
         , ...
         , COUNT(CASE WHEN mo.model = mod8
                      THEN 8 ELSE NULL END) AS count_mod8
      FROM cpe.cpe_inventory iv
    INNER
      JOIN cpe.cpe_manufactuers mf
        ON mf.manufacture_id = iv.manufacture_id
       AND mf.manufacture_name = 'cisco'
    INNER
      JOIN cpe.cpe_model mo
        ON mo.cpe_model_id = iv.model_id
    INNER
      JOIN cpe.cpe_locations lo
        ON lo.cpe_loc ation_id = iv.cpelocation_id
     WHERE iv.cpestatus_id = 4 -- status id 4 = inservice
       AND iv.lastmodifieddts BETWEEN '2009-12-07' AND '2009-12-15'
       AND iv.lastmodifiedby <> 'ntwscan_ext_upd'
    GROUP 
        BY iv.cpe_mkt
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by r937 View Post
    there is no such thing as the TO_DATE function in ANSI SQL
    ....
    BETWEEN '2009-12-07' AND '2009-12-15'
    '2009-12-07' is not an ANSI date literal either

    ANSI date literals need to have the DATE keyword:
    Code:
    BETWEEN DATE '2009-12-07' AND DATE '2009-12-15'

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by shammat View Post
    ANSI date literals need to have the DATE keyword
    i knew that, i just keep forgetting it!!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2010
    Posts
    3

    Talking Thanks!

    That did it!

    Thanks for your help!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •