1

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!

Improve this question
3
  • Well, which column is "location?" Commented Aug 27, 2014 at 20:59
  • Location column? If that is apart of the same table, include it in your select statement. Else join it with another table. Seems straight forward unless I'm missing logic? Commented Aug 27, 2014 at 20:59
  • I apologize, I'll clear that up in the post. 'location' is a value in the 'FIELD_NAME' column for an asset. The VALUE column on that same row has the state associated with that FIELD_NAME 'location'. Commented Aug 27, 2014 at 21:04

1 Answer 1

Reset to default
1

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.
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Thanks for your response. The query is currently not returning any data so I'm looking into why that might be :/
I had RECORD_ID and PARENT_RECORD_ID with some underscores missing. Apart from that, I don't see what's wrong, but I just typed it in Notepad, so I might overlook something.
Yeah I caught those, query is running with no errors. Just not returning anything at the moment.
Maybe the lower function is necessary after all? It doesn't seem to be by your example data, but maybe your real data is different.
Got it working. Error on my part. Thanks for your help!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.