Solved

Crosstab- sum of sums

Posted on 2004-09-15
20
462 Views
Last Modified: 2012-06-22
Hi,
I’m trying to create a report in sql server which basically is a crosstab, where it calculates fep% for each age group.
The table looks something like this:
Mytable:
PANEL      AGE            PAID             ISSUES
N1      10            1            1
N1      20            1            2
N1      16            1            1
P2      23            2            3
P2      29            1            1
P2      31            0            2

The final report should look like the following:

PANEL            AGE 8-21(FEP%)      AGE 22-35 (FEP%)      TOTAL      %
N1            75            0            75      60
P2            0            50            50      40
                  
            75            50            125            

FEP% is calculated for each age group and panel :
= sum(paid)/sum(issues) * 100

for example, For panel N1 : 3/4* 100

And finally total both rows and columns to calculate the final percentage.

I’m new to sql server and could create the age groups but I don’t know how to sum the sums and further on. Could this all be done in one query or a stored procedure is a better approach.  So far, the code for age groups:,
select      panel, sum(case when age between 8 and 21 then 1 else 0 end) as 'age 8-21',
      sum(case when age between 22 and 35 then 1 else 0 end) as 'age 22-35'
      
from       Mytable
group by panel


Can anybody help me please.

Many thanks,
Preeti

0
Comment
Question by:pmandair
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 7
20 Comments
 
LVL 10

Expert Comment

by:imrancs
ID: 12066031
select     panel, (sum(case when age between 8 and 21 then Paid else 0 end)/sum(case when age between 8 and 21 then Issued else 0 end)) * 100 as 'age 8-21(FEP%)',
     (sum(case when age between 22 and 35 then Paid else 0 end)/sum(case when age between 22 and 35 then Issued else 0 end)) * 100 as 'age 22-35(FEP%)',
(Sum(Paid)/Sum(Issued))/ 100 AS 'Total %'
from      Mytable
group by panel


Imran
0
 
LVL 10

Expert Comment

by:imrancs
ID: 12066068
please change the column name from issued to issues or as in  your table

0
 
LVL 10

Expert Comment

by:imrancs
ID: 12066248
you may get the divided by zero error if there is a zero value in devisor

try this code

select     panel, (cast(sum(case when age between 8 and 21 then Paid else NULL end) as numeric(10,2))/cast(sum(case when age between 8 and 21 then Issues else Null end) as numeric(10,2))) * 100.0 as 'age 8-21(FEP%)',
     (cast(sum(case when age between 22 and 35 then Paid else NULL end) as numeric(10,2))/cast(sum(case when age between 22 and 35 then Issues else NULL end) as numeric(10,2))) * 100.0 as 'age 22-35(FEP%)',
Cast(Sum(Paid)as numeric(10,2))/Cast(Sum(Issues)as numeric(10,2))*100.0 'Total %'
from      Mytable
group by panel



Imran
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 10

Expert Comment

by:imrancs
ID: 12066317
the above code may have many zeros after the decimal point use cast them to numeric(10,2) like the following one


select     panel, cast((cast(sum(case when age between 8 and 21 then Paid else NULL end) as numeric(10,2))/cast(sum(case when age between 8 and 21 then Issues else Null end) as numeric(10,2))) * 100 as numeric(10,2)) as 'age 8-21(FEP%)',
     cast((cast(sum(case when age between 22 and 35 then Paid else NULL end) as numeric(10,2))/cast(sum(case when age between 22 and 35 then Issues else NULL end) as numeric(10,2))) * 100 as numeric(10,2))as 'age 22-35(FEP%)',
cast(Cast(Sum(Paid)as numeric(10,2))/Cast(Sum(Issues)as numeric(10,2))*100.0 as numeric(10,2)) as'Total %'
from      Mytable
group by panel


Imran
0
 
LVL 42

Expert Comment

by:Eugene Z
ID: 12066387
your report:


select     panel,
 cast(sum(case when age between 8 and 21 then 1 else 0 end) as varchar(50)) + '        ' +
   
cast(
  cast(
    (cast(sum(case when age between 8 and 21 then Paid else NULL end) as numeric(10,2))/cast(sum(case when age between 8 and 21 then Issues else Null end) as numeric(10,2))) * 100.0
  as int)
as varchar(50))
as 'age 8-21(FEP%)',

 cast(sum(case when age between 22 and 35  then 1 else 0 end) as varchar(50)) + '        ' +
cast(
  cast(
   (cast(sum(case when age between 22 and 35 then Paid else NULL end) as numeric(10,2))/cast(sum(case when age between 22 and 35 then Issues else NULL end) as numeric(10,2))) * 100.0
  as int)
as varchar(50))
as 'age 22-35(FEP%)',
cast(
Cast(Sum(Paid)as numeric(10,2))/Cast(Sum(Issues)as numeric(10,2))*100.0
as int)
'Total %'
from      Mytable
group by panel


0
 

Author Comment

by:pmandair
ID: 12066594
I'm sorry 'Total' and '%' are two separate columns in report. Total being the total for both the rows and columns. 60 in '%' is calcuated from (75/125)*100. The values are not aligned with the column names.
0
 
LVL 10

Expert Comment

by:imrancs
ID: 12066761
here is total column is added

select     panel, cast((cast(sum(case when age between 8 and 21 then Paid else NULL end) as numeric(10,2))/cast(sum(case when age between 8 and 21 then Issues else Null end) as numeric(10,2))) * 100 as numeric(10,2)) as 'age 8-21(FEP%)',
     cast((cast(sum(case when age between 22 and 35 then Paid else NULL end) as numeric(10,2))/cast(sum(case when age between 22 and 35 then Issues else NULL end) as numeric(10,2))) * 100 as numeric(10,2))as 'age 22-35(FEP%)',
Sum(IsNull(Paid,0) + IsNull(Issued,0)) As Total,
cast(Cast(Sum(Paid)as numeric(10,2))/Cast(Sum(Issues)as numeric(10,2))*100.0 as numeric(10,2)) as' %'
from      Mytable
group by panel



Imran
0
 

Author Comment

by:pmandair
ID: 12066790
Imran

You're nearly there...

0
 
LVL 10

Expert Comment

by:imrancs
ID: 12066861
once again chage issued to issues :o)


0
 

Author Comment

by:pmandair
ID: 12066873
Could you calculate total on columns, for each age group?
0
 
LVL 10

Expert Comment

by:imrancs
ID: 12066891
please explain, may be with some example
0
 
LVL 10

Expert Comment

by:imrancs
ID: 12066937
you mean separate sum for Paid and Issues?
0
 

Author Comment

by:pmandair
ID: 12066944
NO, like I calculated in the report , for each age group then total i.e.125
0
 
LVL 10

Expert Comment

by:imrancs
ID: 12066986
that would be a column and will repeat with each row, in your report you can place that column in the footer of the report. And that column i think is Total column that is calcualted as
Sum(IsNull(Paid,0) + IsNull(Issued,0)) As Total


BTW which reporting tool you are using, Crystal Reports ?


Imran
0
 

Author Comment

by:pmandair
ID: 12067018
That's the problem, we're not using any reporting tool. We're trying to do all the calculations if we could on the sql front before exporting it to excel through VB.net where all the formatting would be done. Also, some complicated calculations the ones that can't be handled in SQl.
0
 
LVL 10

Expert Comment

by:imrancs
ID: 12067053
I think you have got your required output and remaining you can do int VB.NET.

If still there is any problem at any side SQL or VB.NET please ask.


Imran
0
 

Author Comment

by:pmandair
ID: 12067082
Also, one last thing is it possible to return 0 in place of Null.
0
 
LVL 10

Accepted Solution

by:
imrancs earned 500 total points
ID: 12067106
here is the revised version to convert NULLs to 0s

select     panel, IsNull(cast((cast(sum(case when age between 8 and 21 then Paid else NULL end) as numeric(10,2))/cast(sum(case when age between 8 and 21 then Issues else Null end) as numeric(10,2))) * 100 as numeric(10,2)),0) as 'age 8-21(FEP%)',
     IsNull(cast((cast(sum(case when age between 22 and 35 then Paid else NULL end) as numeric(10,2))/cast(sum(case when age between 22 and 35 then Issues else NULL end) as numeric(10,2))) * 100 as numeric(10,2)),0) as 'age 22-35(FEP%)',
Sum(IsNull(Paid,0) + IsNull(Issues,0)) As Total,
IsNull(cast(Cast(Sum(Paid)as numeric(10,2))/Cast(Sum(Issues)as numeric(10,2))*100.0 as numeric(10,2)),0) as' %'
from      Mytable
group by panel



Imran
0
 

Author Comment

by:pmandair
ID: 12067125
Thank you!
0
 
LVL 10

Expert Comment

by:imrancs
ID: 12067146
You are Welcome!!!

Glad if could help.  :o)

Good Luck and happy reporting without reporting tool ;o)

Imran
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How can I find this data? 3 31
SQL Server Resume 5 46
Running Total Using new MS SQL Function 21 53
T-SQL: How to extract records into a new table 7 24
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

697 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question