Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.
Course Of The Month
4 days, 1 hour left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Crosstab- sum of sums
Posted on 2004-09-15
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
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
[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
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
(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
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(ca
(cast(sum(case when age between 22 and 35 then Paid else NULL end) as numeric(10,2))/cast(sum(ca
Cast(Sum(Paid)as numeric(10,2))/Cast(Sum(Is
from Mytable
group by panel
Imran
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(ca
(cast(sum(case when age between 22 and 35 then Paid else NULL end) as numeric(10,2))/cast(sum(ca
Cast(Sum(Paid)as numeric(10,2))/Cast(Sum(Is
from Mytable
group by panel
Imran
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(ca
cast((cast(sum(case when age between 22 and 35 then Paid else NULL end) as numeric(10,2))/cast(sum(ca
cast(Cast(Sum(Paid)as numeric(10,2))/Cast(Sum(Is
from Mytable
group by panel
Imran
select panel, cast((cast(sum(case when age between 8 and 21 then Paid else NULL end) as numeric(10,2))/cast(sum(ca
cast((cast(sum(case when age between 22 and 35 then Paid else NULL end) as numeric(10,2))/cast(sum(ca
cast(Cast(Sum(Paid)as numeric(10,2))/Cast(Sum(Is
from Mytable
group by panel
Imran
Active 4 days ago
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(ca
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(ca
as int)
as varchar(50))
as 'age 22-35(FEP%)',
cast(
Cast(Sum(Paid)as numeric(10,2))/Cast(Sum(Is
as int)
'Total %'
from Mytable
group by panel
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(ca
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(ca
as int)
as varchar(50))
as 'age 22-35(FEP%)',
cast(
Cast(Sum(Paid)as numeric(10,2))/Cast(Sum(Is
as int)
'Total %'
from Mytable
group by panel
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.
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(ca
cast((cast(sum(case when age between 22 and 35 then Paid else NULL end) as numeric(10,2))/cast(sum(ca
Sum(IsNull(Paid,0) + IsNull(Issued,0)) As Total,
cast(Cast(Sum(Paid)as numeric(10,2))/Cast(Sum(Is
from Mytable
group by panel
Imran
select panel, cast((cast(sum(case when age between 8 and 21 then Paid else NULL end) as numeric(10,2))/cast(sum(ca
cast((cast(sum(case when age between 22 and 35 then Paid else NULL end) as numeric(10,2))/cast(sum(ca
Sum(IsNull(Paid,0) + IsNull(Issued,0)) As Total,
cast(Cast(Sum(Paid)as numeric(10,2))/Cast(Sum(Is
from Mytable
group by panel
Imran
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
Sum(IsNull(Paid,0) + IsNull(Issued,0)) As Total
BTW which reporting tool you are using, Crystal Reports ?
Imran
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.
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
If still there is any problem at any side SQL or VB.NET please ask.
Imran
here is the revised version to convert NULLs to 0s
select panel, IsNull(cast((cast(sum(case
IsNull(cast((cast(sum(case
Sum(IsNull(Paid,0) + IsNull(Issues,0)) As Total,
IsNull(cast(Cast(Sum(Paid)
from Mytable
group by panel
Imran
select panel, IsNull(cast((cast(sum(case
IsNull(cast((cast(sum(case
Sum(IsNull(Paid,0) + IsNull(Issues,0)) As Total,
IsNull(cast(Cast(Sum(Paid)
from Mytable
group by panel
Imran
Featured Post
From IT major incidents to manufacturing line slowdowns, every business process generates insights that need to reach the people required to take action. You need a platform that integrates with your business tools to create fully enabled DevOps toolchains.
You need xMatters.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes. We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses
Course of the Month4 days, 1 hour left to enroll
690 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.