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.

Related Posts


One of my interests about a decade ago was agorics, the study of computational markets.  Mark S. Miller and K. Eric Drexler pushed this idea in the late 1990s and collected a fair portion of the work on the topic on Drexler’s website.  A sample from the section on computation and economic order: Trusting objects […]

Read More

Readings In Database Systems

Curated SQL is taking Thanksgiving off.  If you are looking for some good reading, check out Readings in Database Systems, 5th Edition.

Read More

1 Comment

  • SQLWayne on 2016-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


January 2016
« Dec Feb »