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

Agorics

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

Categories

January 2016
MTWTFSS
« Dec Feb »
 123
45678910
11121314151617
18192021222324
25262728293031