|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
best way to get percentagesthis may look familiar to some of you but i have a new question regarding this: i have a small table that looks like the following: gender/age ------------- male 18 female 32 male 40 female 25 female 26 female 30 how can i store or display horizontally like the following: the count for each gender in respective age categories ageCategory1 ageCategory2 ageCategory3 male 1 0 1 female 2 2 0 Legend: 20-29 Category1 30-39 Category2 40-49 Category3 Here's my new question if i may: with thanks to the ones who replied to previous post, now that i have the preceding table created What's the best way to get percentage values for all columns, % totals on the right, and %totals on the bottom? thanks, rodchar The best way is to use a client tool (like Excel) to do this. Yoiu can
certainly hammer out a kludge in SQL to do this, but why spend the effort when you can build a query in Excel in a matter of minutes? Stu rodchar wrote: Show quote > hey all, > > this may look familiar to some of you but i have a new question regarding > this: > > i have a small table that looks like the following: > > gender/age > ------------- > male 18 > female 32 > male 40 > female 25 > female 26 > female 30 > > how can i store or display horizontally like the following: > the count for each gender in respective age categories > > ageCategory1 ageCategory2 ageCategory3 > male 1 0 1 > female 2 2 0 > > Legend: > 20-29 Category1 > 30-39 Category2 > 40-49 Category3 > > Here's my new question if i may: > with thanks to the ones who replied to previous post, now that i have the > preceding table created > What's the best way to get percentage values for all columns, % totals on > the right, and %totals on the bottom? > > > thanks, > rodchar You're really pushing this into the reahm of presentation. That is what
presentation tools (such as SQL Server Reporting Services) do best. -- Show quoteArnie Rowland "To be successful, your heart must accompany your knowledge." "rodchar" <rodc***@discussions.microsoft.com> wrote in message news:3E3E9622-B896-43A3-B186-8781EFEC3C80@microsoft.com... > hey all, > > this may look familiar to some of you but i have a new question regarding > this: > > i have a small table that looks like the following: > > gender/age > ------------- > male 18 > female 32 > male 40 > female 25 > female 26 > female 30 > > how can i store or display horizontally like the following: > the count for each gender in respective age categories > > ageCategory1 ageCategory2 ageCategory3 > male 1 0 1 > female 2 2 0 > > Legend: > 20-29 Category1 > 30-39 Category2 > 40-49 Category3 > > Here's my new question if i may: > with thanks to the ones who replied to previous post, now that i have the > preceding table created > What's the best way to get percentage values for all columns, % totals on > the right, and %totals on the bottom? > > > thanks, > rodchar awesome, thank you for the wisdom.
Show quote "Arnie Rowland" wrote: > You're really pushing this into the reahm of presentation. That is what > presentation tools (such as SQL Server Reporting Services) do best. > > -- > Arnie Rowland > "To be successful, your heart must accompany your knowledge." > > > > "rodchar" <rodc***@discussions.microsoft.com> wrote in message > news:3E3E9622-B896-43A3-B186-8781EFEC3C80@microsoft.com... > > hey all, > > > > this may look familiar to some of you but i have a new question regarding > > this: > > > > i have a small table that looks like the following: > > > > gender/age > > ------------- > > male 18 > > female 32 > > male 40 > > female 25 > > female 26 > > female 30 > > > > how can i store or display horizontally like the following: > > the count for each gender in respective age categories > > > > ageCategory1 ageCategory2 ageCategory3 > > male 1 0 1 > > female 2 2 0 > > > > Legend: > > 20-29 Category1 > > 30-39 Category2 > > 40-49 Category3 > > > > Here's my new question if i may: > > with thanks to the ones who replied to previous post, now that i have the > > preceding table created > > What's the best way to get percentage values for all columns, % totals on > > the right, and %totals on the bottom? > > > > > > thanks, > > rodchar > > > I'm glad to be appreciated.
-- Show quoteArnie Rowland "To be successful, your heart must accompany your knowledge." "rodchar" <rodc***@discussions.microsoft.com> wrote in message news:85148E50-3F94-46EC-B6CB-4F9EF21B2BD5@microsoft.com... > awesome, thank you for the wisdom. > > "Arnie Rowland" wrote: > >> You're really pushing this into the reahm of presentation. That is what >> presentation tools (such as SQL Server Reporting Services) do best. >> >> -- >> Arnie Rowland >> "To be successful, your heart must accompany your knowledge." >> >> >> >> "rodchar" <rodc***@discussions.microsoft.com> wrote in message >> news:3E3E9622-B896-43A3-B186-8781EFEC3C80@microsoft.com... >> > hey all, >> > >> > this may look familiar to some of you but i have a new question >> > regarding >> > this: >> > >> > i have a small table that looks like the following: >> > >> > gender/age >> > ------------- >> > male 18 >> > female 32 >> > male 40 >> > female 25 >> > female 26 >> > female 30 >> > >> > how can i store or display horizontally like the following: >> > the count for each gender in respective age categories >> > >> > ageCategory1 ageCategory2 ageCategory3 >> > male 1 0 1 >> > female 2 2 0 >> > >> > Legend: >> > 20-29 Category1 >> > 30-39 Category2 >> > 40-49 Category3 >> > >> > Here's my new question if i may: >> > with thanks to the ones who replied to previous post, now that i have >> > the >> > preceding table created >> > What's the best way to get percentage values for all columns, % totals >> > on >> > the right, and %totals on the bottom? >> > >> > >> > thanks, >> > rodchar >> >> >> And this is the kludge that the other guys were talking about
Wouldn't have written it if I weren't so bored :) SELECT gender, SUM(CASE WHEN age BETWEEN 20 AND 29 THEN 1 ELSE 0 END) / case when (gender is null) then (select SUM(CASE WHEN age BETWEEN 20 AND 49 THEN 1.0 ELSE 0 END) as tot from tbl1) /100 else 1 end AS ageCategory1, SUM(CASE WHEN age BETWEEN 30 AND 39 THEN 1 ELSE 0 END) /case when (gender is null) then (select SUM(CASE WHEN age BETWEEN 20 AND 49 THEN 1.0 ELSE 0 END) as tot from tbl1) /100 else 1 end AS ageCategory2, SUM(CASE WHEN age BETWEEN 40 AND 49 THEN 1 ELSE 0 END)/case when (gender is null) then (select SUM(CASE WHEN age BETWEEN 20 AND 49 THEN 1.0 ELSE 0 END) as tot from tbl1) /100 else 1 end AS ageCategory3, SUM(CASE WHEN age BETWEEN 20 AND 49 THEN 1 ELSE 0 END) /(select SUM(CASE WHEN age BETWEEN 20 AND 49 THEN 1.0 ELSE 0 END) as tot from tbl1) *100 as total FROM tbl1 a GROUP BY gender with rollup Omnibuzz wrote:
Show quote > And this is the kludge that the other guys were talking about create table genderdetail(> Wouldn't have written it if I weren't so bored :) > > SELECT gender, > SUM(CASE WHEN age BETWEEN 20 AND 29 THEN 1 ELSE 0 END) / case when (gender > is null) then (select SUM(CASE WHEN age BETWEEN 20 AND 49 THEN 1.0 ELSE 0 > END) as tot from tbl1) /100 else 1 end AS > ageCategory1, > SUM(CASE WHEN age BETWEEN 30 AND 39 THEN 1 ELSE 0 END) /case when (gender > is null) then (select SUM(CASE WHEN age BETWEEN 20 AND 49 THEN 1.0 ELSE 0 > END) as tot from tbl1) /100 else 1 end AS > ageCategory2, > SUM(CASE WHEN age BETWEEN 40 AND 49 THEN 1 ELSE 0 END)/case when (gender > is null) then (select SUM(CASE WHEN age BETWEEN 20 AND 49 THEN 1.0 ELSE 0 > END) as tot from tbl1) /100 else 1 end AS > ageCategory3, > SUM(CASE WHEN age BETWEEN 20 AND 49 THEN 1 ELSE 0 END) /(select SUM(CASE > WHEN age BETWEEN 20 AND 49 THEN 1.0 ELSE 0 END) as tot from tbl1) *100 as > total > FROM tbl1 a > GROUP BY gender > with rollup > > > -- > -Omnibuzz (The SQL GC) > > http://omnibuzz-sql.blogspot.com/ gender varchar(100), age int) insert into genderdetail values('male',28) insert into genderdetail values('male',40) insert into genderdetail values('female',32) insert into genderdetail values('female',25) insert into genderdetail values('female',26) insert into genderdetail values('female',30) create table range(low int ,high int, category varchar(25)) insert into range values(20,29, 'category1') insert into range values(30,39,'category2') insert into range values(40,49,'category3') select gender , sum([category1]) category1 ,sum([category2]) as category2,sum([category3]) as category3 from (select g.gender as gender ,g.age as age, r.category as category from genderdetail g inner join range r on g.age between r.low and r.high) p pivot ( count(age) for category in ( [category1],[category2],[category3]) )as pvt group by gender order by gender desc Regards Amish Shah amish wrote:
Show quote > Omnibuzz wrote: I forgot about percentage part> > And this is the kludge that the other guys were talking about > > Wouldn't have written it if I weren't so bored :) > > > > SELECT gender, > > SUM(CASE WHEN age BETWEEN 20 AND 29 THEN 1 ELSE 0 END) / case when (gender > > is null) then (select SUM(CASE WHEN age BETWEEN 20 AND 49 THEN 1.0 ELSE 0 > > END) as tot from tbl1) /100 else 1 end AS > > ageCategory1, > > SUM(CASE WHEN age BETWEEN 30 AND 39 THEN 1 ELSE 0 END) /case when (gender > > is null) then (select SUM(CASE WHEN age BETWEEN 20 AND 49 THEN 1.0 ELSE 0 > > END) as tot from tbl1) /100 else 1 end AS > > ageCategory2, > > SUM(CASE WHEN age BETWEEN 40 AND 49 THEN 1 ELSE 0 END)/case when (gender > > is null) then (select SUM(CASE WHEN age BETWEEN 20 AND 49 THEN 1.0 ELSE 0 > > END) as tot from tbl1) /100 else 1 end AS > > ageCategory3, > > SUM(CASE WHEN age BETWEEN 20 AND 49 THEN 1 ELSE 0 END) /(select SUM(CASE > > WHEN age BETWEEN 20 AND 49 THEN 1.0 ELSE 0 END) as tot from tbl1) *100 as > > total > > FROM tbl1 a > > GROUP BY gender > > with rollup > > > > > > -- > > -Omnibuzz (The SQL GC) > > > > http://omnibuzz-sql.blogspot.com/ > > > > create table genderdetail( > gender varchar(100), > age int) > > insert into genderdetail values('male',28) > insert into genderdetail values('male',40) > insert into genderdetail values('female',32) > insert into genderdetail values('female',25) > insert into genderdetail values('female',26) > insert into genderdetail values('female',30) > > > create table range(low int ,high int, category varchar(25)) > > insert into range values(20,29, 'category1') > insert into range values(30,39,'category2') > insert into range values(40,49,'category3') > > select gender > , sum([category1]) category1 ,sum([category2]) as > category2,sum([category3]) as category3 > from > (select g.gender as gender ,g.age as age, r.category as category from > genderdetail g inner join range r on g.age between r.low and r.high) > p > pivot > ( > count(age) > for category in ( [category1],[category2],[category3]) > )as pvt > group by gender > order by gender desc > > > Regards > Amish Shah Please change the query to get percentage also select gender , case when grouping(gender) = 1 then sum([category1])*100.0/sum([category1]+[category2]+[category3]) else sum([category1]) end category1 , case when grouping(gender) = 1 then sum([category2])*100.0/sum([category1]+[category2]+[category3]) else sum([category2]) end category2 , case when grouping(gender) = 1 then sum([category3])*100.0/sum([category1]+[category2]+[category3]) else sum([category3]) end category3 , sum([category1]+[category2]+[category3])*100.0/ (select count(age) from genderdetail) from (select g.gender as gender ,g.age as age, r.category as category from genderdetail g inner join range r on g.age between r.low and r.high) p pivot ( count(age) for category in ( [category1],[category2],[category3]) )as pvt group by gender with cube Regards Amish Shah Shahamishm.tripod.com Why spend the effort when you can do this a matter of minutes with RAC.
Percentages are a simple option with any dynamic or static crosstab.%'s also have many associated options.Check out RAC @: Oops:)
Why spend the effort when you can do this a matter of minutes with RAC. Percentages are a simple option with any dynamic or static crosstab.%'s also have many associated options.Check out RAC @: www.rac4sql.net I support Dataphor (www.alphora.com) as a RAD and a true Relational dbms. I support MS Sql Server as a storage device for Dataphor.  |
|||||||||||||||||||||||
Other interesting topics