Post reply

Tricky Calculation, Multiple Query?!?!

  • November 7, 2011 at 10:51 am

    #246339

    Guys,

    First off I just want to say that I am not looking for anyone to do this for me, just pointing in the right direction.

    I have a a large set of data containing multiple countries, each with a designated value (I have made a scaled down version for the forum) as below

    CountryLevel

    England2

    Ireland3

    Scotland1

    Scotland2

    England1

    England1

    England3

    Wales3

    Ireland2

    Wales2

    Ireland 3

    England1

    England2

    Total26

    I would like to break this data out so it is Grouped by country and split out by level like this:

    Country# L1L2L3

    England6321

    Ireland3012

    Scotland2110

    Wales2011

    I am able to count the number of locations and group them by country, and produce a column for level 1 or level 2 or level 3 (the real data has 15 levels and 190 countries and 142k records) The problem arises when I want to create a query that will have all the levels as headers and the number of locations.

    Would a nested query work here, or could I create 15 select queryies and name them A as B as etc and then combine them at the end?! I guess this is essentially a pivot table, so maybe there is an easy way to go about it within SQL that I am not aware of?!

    Thanks for taking the time out to look! 😀

    Also for future ref is there a better way to display tables within the forum using the IFCode?!

  • November 7, 2011 at 10:58 am

    #1404105

    A series of 15 CASE statment will do. Or dymanic query is also a route u can take.

    Example of CASE statement

    select country,

    sum ( case when level = 1 then 1 else 0 end ) level 1 ,

    sum ( case when level = 2 then 1 else 0 end ) level 2 ,

    sum ( case when level = 3 then 1 else 0 end ) level 3 ,

    .....................................

    sum ( case when level = 10 then 1 else 0 end ) level 10 ,

    sum ( case when level = 11 then 1 else 0 end ) level 11 ,

    ....................................

    sum ( case when level = 15 then 1 else 0 end ) level 15

    from <YourTable>

    group by country

  • November 7, 2011 at 11:29 am

    #1404117

    agree with CC...if you need "total" as well

    select country,

    COUNT (level) as total,

    sum ( case when level = 1 then 1 else 0 end ) level_1 ,

    sum ( case when level = 2 then 1 else 0 end ) level_2 ,

    sum ( case when level = 3 then 1 else 0 end ) level_3 ,

    --................................................ and so on

    sum ( case when level = 10 then 1 else 0 end ) level_10 ,

    sum ( case when level = 11 then 1 else 0 end ) level_11 ,

    sum ( case when level = 15 then 1 else 0 end ) level_15

    from <Yourtable>

    group by country

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • November 7, 2011 at 1:00 pm

    #1404151

    You could also use PIVOT.

    CREATE TABLE dbo.CountryTest (

    Countryvarchar(50) NOT NULL,

    [Level]int NOT NULL) ;

    GO

    INSERT INTO dbo.CountryTest

    VALUES ('England', 2), ('Ireland', 3), ('Scotland', 1), ('Scotland', 2),

    ('England', 1), ('England', 1), ('England', 3), ('Wales', 3), ('Ireland', 2),

    ('Wales', 2), ('Ireland', 3), ('England', 1), ('England', 2)

    SELECT Country,

    L1 = ISNULL([1], 0),

    L2 = ISNULL([2], 0),

    L3 = ISNULL([3], 0),

    L4 = ISNULL([4], 0),

    L5 = ISNULL([5], 0),

    L6 = ISNULL([6], 0),

    L7 = ISNULL([7], 0),

    L8 = ISNULL([8], 0),

    L9 = ISNULL([9], 0),

    L10 = ISNULL([10], 0),

    L11 = ISNULL([11], 0),

    L12 = ISNULL([12], 0),

    L13 = ISNULL([13], 0),

    L14 = ISNULL([14], 0),

    L15 = ISNULL([15], 0)

    FROM (

    SELECT Country, [Level], Unit = 1

    FROM dbo.CountryTest ) b

    PIVOT (SUM(Unit) FOR [Level] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15])) p

    DROP TABLE dbo.CountryTest

  • November 7, 2011 at 4:58 pm

    #1404217

    Scott Coleman (11/7/2011)

    You could also use PIVOT.

    Please see the following for why you might want to consider good ol' fashioned CROSS TABs instead.

    http://www.sqlservercentral.com/articles/T-SQL/63681/

    Of most importance is the performance chart near the end of the article.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 8, 2011 at 2:37 am

    #1404322

    Morning Guys,

    Thanks for your responses, I will give both a go as it does not hurt to learn how to do both approaches!

    I will let you know how it goes.

    Thanks again for your time!

    Dave

    😀

  • November 8, 2011 at 3:28 am

    #1404349

    Excellent,

    They both work really well!

    Now to get these numbers as a %, is there someway I can include the calculation within the SUM statement?!

    select CONTRY_NAME_LC,

    COUNT (GeoResolutionCode) as Total,

    SUM(case when GeoResolutionCode = 1 then 1 else 0 end) Coordinate,

    Sort of like SUM((case when GeoResolutionCode = 1 then 1 else 0 end)/(Total*100)) Coordinate,

    For this to work I guess I need to nest all the case statements after we have calculated the total so I can refer to it in the query?!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply