Count Question

MRLancaster · #1 (**permalink**) 01-12-10, 23:12

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

r937 · #2 (**permalink**) 01-12-10, 23:29

Quote:

Originally Posted by MRLancaster

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)

MRLancaster · #3 (**permalink**) 01-13-10, 05:43

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

r937 · #4 (**permalink**) 01-13-10, 12:31

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

shammat · #5 (**permalink**) 01-13-10, 13:41

Quote:

Originally Posted by r937

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'

r937 · #6 (**permalink**) 01-13-10, 13:43

Quote:

Originally Posted by shammat

ANSI date literals need to have the DATE keyword

i knew that, i just keep forgetting it!!!

MRLancaster · #7 (**permalink**) 01-13-10, 15:57

That did it!

Thanks for your help!

Internet Services

Web Development

Digital Marketing

Consumer Tech