CODEWITHSUNDEEP
sqloracle-databasecount
ad2387
ad2387

Reputation: 581

Oracle SQL Count based on conditions

I'm trying to create a query to get a count of the number of cables in each asset location. The flow is Project->Asset->Cable. One project can have multiple assets with multiple cables associated to these assets. Below is a snapshot of the data. 

RECORD_ID    PARENT_RECORD_ID    FORM_NAME    PARENT_FORM    FIELD_NAME   VALUE
   1604            1603            asset         project       serial     b345s
   1604            1603            asset         project       location   OH
   1605            1604            cable         asset         zone       green
   1606            1603            asset         project       location   NY
   1607            1606            cable         asset         port       2

As you can see above, at the cable level, the PARENT_RECORD_ID is the "RECORD_ID" of the asset. This is just a snapshot as there are thousands of rows, with a bunch of data in between but I tried to only show relevant data.

I need to figure out a way to roll up the number of cables per asset, and then group the assets by 'location'. The 'location' is a value in the FIELD_NAME column for the asset, with the value associated with it in the VALUE column. For example, above, the asset ID 1604, the 'location' is 'OH'.

So far I've been able to get a count of the total number of cables. I just need to figure out how to break these down by the 'location' of their 'asset'.

SELECT
  COUNT(DISTINCT RECORD_ID) "Number of Cables"
FROM 
  PROJECT_TABLE
WHERE 
  LOWER(FORM_NAME) = 'cable'

Current result of above query:

Number of Cables
     3137

What I would like the result to look like:

Location     Number of Cables
  OH                552
  NY               1042
  MI                49
  CA               1494

Any help is greatly appreciated! Thanks!

Upvotes: 1

Views: 2852

Answers (1)

GolezTrol
GolezTrol

Reputation: 116190

This should do the trick.

select
  ass.VALUE as LOCATION,
  COUNT(DISTINCT cab.RECORD_ID) as CABLE_COUNT
from
  PROJECT_TABLE cab
  inner join PROJECT_TABLE ass on ass.RECORD_ID = cab.PARENT_RECORD_ID
where
  cab.FORM_NAME = 'cable' and
  ass.FORM_NAME = 'asset' and -- should always be the case?
  ass.FIELD_NAME = 'location'
group by
  ass.VALUE

Some tips:

  • Use aliases for tables. In this case you must, because the same table is used twice, but I also do it when I don't have to.
  • If you use aliases, make sure to use abbreviations like ass if you are querying assets. It's one of the little joys in life. :)
  • I've removed lower. If it's not needed, leave it out. Without the need to call functions, you can fully use the indexes and your query will be faster. Make sure all values have the same case, or better, make a Forms table that contains the name with a numeric ID, and use that ID in the other tables. (Google Surrogate keys). Alternatively, have a look at function indexes. This allows Oracle to build an index bases on a function result instead of a normal value.
  • If there is only one record for a cable, you don't need distinct in the count, and you won't need the condition ass.FORM_NAME = 'asset', so your query is optimized a little more.
  • A combined index on FORM_NAME and VALUE is beneficial, but only as long as you need both conditions (see comment in query) and don't use lower (see two remarks above).
  • In general, all-purpose tables like these are bad practice. If you have separate tables for assets, projects and locations and make a proper, normalized model out of it, you will have a better, more maintainable data model, that is much easier to read and to use.

Upvotes: 1

Related Questions