Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat. The purpose of this eBook is to educate the reader about ransomware attacks.
COURSE of the MONTH
12 days, 15 hours 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 today
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
The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed.
Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Suggested Courses
Course of the Month12 days, 15 hours left to enroll
777 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.