best way to display breakouts with sum totals?

Question

best way to display breakouts with sum totals?

foscsamuels

Mr or Mrs. 500

Points: 560
More actions
February 22, 2010 at 9:34 am

#211541

I'm trying to think of the best way to show breakouts of a particular column with a sum total following.
the live column has multiple values, i need to show the percentages of each value compared to the total amount of rows.
I know i can perform a group by the one necessary column to show the information.
Maybe there's a better way to do this? like maybe use cube or other functions?

Viewing 10 posts - 1 through 9 (of 9 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

Please be a little more specific about what you're trying to do.

If you provide ready to use sample data as described in the first link in my signature I'm sure you'll get a reply almost instantly.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

foscsamuels Mr or Mrs. 500 Points: 560 More actions · Answer 2

id - live - disabled

===================

1 Y Y

2 Y N

3 N Y

4 N N

5 I Y

6 I N

7 I Y

8 I N

[/CODE]

I need to show percents of certain info. I have two queries that output the numbers to calculate the percent. Is it possible to write two queries in one statement to show the percent?

select count(live) form mytable where live <> 'i'

select count(live) form mytable where live = 'y' and disabled = 'n'

LutzM SSC Guru Points: 107049 More actions · Answer 3

I'm still not 100% sure what value your percentage calculation will be based on. But maybe the following code will get you started.

Please note how I provided sample data in a ready to use format...

DECLARE @tbl TABLE

(

id INT, live CHAR(1), disab CHAR(1)

)

INSERT INTO @tbl

SELECT 1 , 'Y' , 'Y' UNION ALL

SELECT 2 , 'Y' , 'N' UNION ALL

SELECT 3 , 'N' , 'Y' UNION ALL

SELECT 4 , 'N' , 'N' UNION ALL

SELECT 5 , 'I' , 'Y' UNION ALL

SELECT 6 , 'I' , 'N' UNION ALL

SELECT 7 , 'I' , 'Y' UNION ALL

SELECT 8 , 'I' , 'N'

SELECT *

FROM @tbl

; WITH cte AS

(

SELECT COUNT(live) AS live_not_i

FROM @tbl

WHERE live <> 'i'

),

cte2 AS

(

SELECT COUNT(live) live_eq_y

FROM @tbl

WHERE live = 'y' AND disab = 'n'

)

SELECT (1.0*live_eq_y)/live_not_i * 100

FROM cte

CROSS JOIN cte2

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

foscsamuels Mr or Mrs. 500 Points: 560 More actions · Answer 4

this is what i see

declare variable as a table

populate a table varible with data

create common table expressions using two queries

calculate percents using select statement with a cross join.

i'm not to keen on using cte's with cross join. what is the last select doing?

LutzM SSC Guru Points: 107049 More actions · Answer 5

First and second cte are used to populate the count() values as requested.

The CROSS JOIN will result in a cartesian product of all rows from the first cte (cte) and the second cte (cte2).

Since both cte's (or subselects) only have one row, it's basically nothing else then the formula (1.0*live_eq_y)/live_not_i * 100.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

foscsamuels Mr or Mrs. 500 Points: 560 More actions · Answer 6

lmu92, you've been a big help this far. I appreciate you sharing your knowledge.

I tried rewriting the calculation for the percent as (live_eq_y/live_not_i) * 100

When I try this it returns zero, but wasn't sure why.

Also, when I write the query the way you have posted, it works perfectly.

matzer SSCrazy Eights Points: 9727 More actions · Answer 7

foscsamuels (2/23/2010)
I tried rewriting the calculation for the percent as (live_eq_y/live_not_i) * 100
When I try this it returns zero, but wasn't sure why.

You get zero because all the operands in your expression are INT's. When live_eq_y < live_not_i then lev_eq_y/live_not_i = 0. Lutz resolved this problem by multiplying live_eq_y with 1.0 which turns the whole expression into a floating point expression. If you want a whole number as a result then use (lev_eq_y * 100) / live_not_i.

The solution Lutz provided is great because it followed your specs very closely, i.e. compute an expression based on the result of two queries. However, the solution requires two table scan. It's easy to combine the query into a single table scans:

SELECT

COUNT(CASE WHEN live = 'Y' AND disab = 'N' THEN 1 END) * 100. / COUNT(*)

FROM

@tbl

WHERE

live <> 'I'

Peter

LutzM SSC Guru Points: 107049 More actions · Answer 8

Peter Brinkhaus (2/23/2010)

...
SELECT
COUNT(CASE WHEN live = 'Y' AND disab = 'N' THEN 1 END) * 100. / COUNT(*)
FROM
@tbl
WHERE
live <> 'I'
Peter

Excellent job, Peter!!

If I had the choice between your solution and mine, I'd throw mine away without a second thought...

I missed the obvious by following a predefined solution path instead of focusing on the expected result. :blush: Something that should not / must not happen...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

matzer SSCrazy Eights Points: 9727 More actions · Answer 9

lmu92 (2/23/2010)

Peter Brinkhaus (2/23/2010)
...
SELECT
COUNT(CASE WHEN live = 'Y' AND disab = 'N' THEN 1 END) * 100. / COUNT(*)
FROM
@tbl
WHERE
live <> 'I'
Peter
Excellent job, Peter!!
If I had the choice between your solution and mine, I'd throw mine away without a second thought...
I missed the obvious by following a predefined solution path instead of focusing on the expected result. :blush: Something that should not / must not happen...

No, no, you didn't miss the obvious. You started with the obvious. It's what I usely do too, then I start to look for a better/faster solution (if there is a reason to).

Peter