Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 493
  • Last Modified:

Crosstab- sum of sums

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
pmandair
Asked:
pmandair
  • 12
  • 7
1 Solution
 
imrancsCommented:
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
 
imrancsCommented:
please change the column name from issued to issues or as in  your table

0
 
imrancsCommented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
imrancsCommented:
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
 
Eugene ZCommented:
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
 
pmandairAuthor Commented:
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
 
imrancsCommented:
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
 
pmandairAuthor Commented:
Imran

You're nearly there...

0
 
imrancsCommented:
once again chage issued to issues :o)


0
 
pmandairAuthor Commented:
Could you calculate total on columns, for each age group?
0
 
imrancsCommented:
please explain, may be with some example
0
 
imrancsCommented:
you mean separate sum for Paid and Issues?
0
 
pmandairAuthor Commented:
NO, like I calculated in the report , for each age group then total i.e.125
0
 
imrancsCommented:
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
 
pmandairAuthor Commented:
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
 
imrancsCommented:
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
 
pmandairAuthor Commented:
Also, one last thing is it possible to return 0 in place of Null.
0
 
imrancsCommented:
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
 
pmandairAuthor Commented:
Thank you!
0
 
imrancsCommented:
You are Welcome!!!

Glad if could help.  :o)

Good Luck and happy reporting without reporting tool ;o)

Imran
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 12
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now