Answered: Pulling Data Into One Line (Aggregation)
I struggled for the longest time to do this but eventually I started using DERIVED Tables and Sub queries within them if needed. I like choking down all the queries in the selects joining them and having your result set select there to choose from all the aliases, it also resolves while doing this. So much easier IMO than using CTE's or TEMP Tables. I was big on temp tables for a while...
I'm curious though if you want to count a type of criteria in a column do you use a standard case in your inner query choking it down just for those particular counts? Then do another case on the other criteria and END AS with the alias name?
CASE WHEN COLUMN1 = PIZZA THEN COUNT(COLUMN1) END AS PIZZACOUNT
CASE WHEN COLUMN1 = ROOTBEER THEN COUNT(COLUMN!) END AS ROOTBEERCOUNT
I'm exploring other ways of doing this, but right now this is the only way I know. Are IIF's better in these situations? Any other solutions?
MC - Just a follow up, in my top select in my derived table I wanted to calculate percentage off of that. After reviewing a few sites, I found this which works well. SUM(CAST(COLUMN1 AS DECIMAL) ) / SUM(CAST(COLUMN2 AS DECIMAL)) as PRC_COLUMN. You probably already knew this but thought I would share.