Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6

    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?

    E.G.

    CASE WHEN COLUMN1 = PIZZA THEN COUNT(COLUMN1) END AS PIZZACOUNT
    CASE WHEN COLUMN1 = ROOTBEER THEN COUNT(COLUMN!) END AS ROOTBEERCOUNT
    ETC...........

    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?

  2. Best Answer
    Posted by MCrowley

    "In general, the way to flatten out the counts is to do something along the lines of:
    Code:
    select COLUMN1, 
        sum(case when column1 = 'PIZZA' then 1 else 0 end) as PizzaCount,
        sum(case when column1 = 'ROOTBEER' then 1 else 0 end) as RootBeerCount,
    ....
    There may be a way to do this using PIVOT, perhaps, but I am not too good with that operator."


  3. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    In general, the way to flatten out the counts is to do something along the lines of:
    Code:
    select COLUMN1, 
        sum(case when column1 = 'PIZZA' then 1 else 0 end) as PizzaCount,
        sum(case when column1 = 'ROOTBEER' then 1 else 0 end) as RootBeerCount,
    ....
    There may be a way to do this using PIVOT, perhaps, but I am not too good with that operator.

  4. #3
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Fantastic! Thanks for the follow up MCROWLEY! That is perfect......

  5. #4
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    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.

  6. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    A tip from Terry Purcell years ago. Use ELSE NULL instead of ELSE 0. A zero is added whereas a NULL is ignored.
    Dave

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •