Tricky Calculation, Multiple Query?!?!

Question

Post reply

Tricky Calculation, Multiple Query?!?!

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

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

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

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

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 2

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

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 3

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

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 4

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)

methexis SSC Eights! Points: 815 More actions · Answer 5

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

😀

methexis SSC Eights! Points: 815 More actions · Answer 6

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?!