Press "Enter" to skip to content

Finding Object Counts

SQLWayne shows how to break down counts of objects by type:

And while it did the trick, I was wanting, for no particular reason, to also have the total number of objects and the percentage.  Again, no particular reason.  It might be able to be done with a window function, but that is also something that I have limited familiarity with, so I decided to approach it as a CTE.  And it works nicely.  The objs CTE gives me a count of each object type while the tots CTE gives me the count of all objects.  By giving each CTE a column with the value of 1, it’s easy to join them together then calculate a percentage.

That’s one of the nicest things about SQL as a language:  you access metadata the same way you access regular data, so that technique can be used to query other data sets as well.

One Comment

  1. SQLWayne
    SQLWayne2016-01-18

    Thank you for the linkback, Kevin. I love the concept of your blog and expect I’ll be keeping an eye on it! There’s so much information online that it’s hard to find quality info, your blog fills a nice niche.

Comments are closed.