I am trying to calculate a value using an IF in a SQL statement. Below is what I am trying. I am not sure what is wrong with the IF. The error says missing operator.
SELECT
cr_401k_data_sheet1.`Department`,
SUM (cr_401k_data_sheet1.`Gross`) As Gross,
SUM (cr_401k_data_sheet1.`Contribution`) As Contribution,
SUM( cr_401k_data_sheet1.Gross*.05) As Limit,
(if SUM(Contribution) <> Limit then Contribution) As Match
FROM
`cr_401k_data_sheet1` cr_401k_data_sheet1
GROUP BY
cr_401k_data_sheet1.`Department`,
Thanks in advance,
vmonyou cannot use IF in crystal SQL, infect you cannot use IF in any SQL, untill you are using a stored procedure
how ever you can do this calulation in Crystal, from your query it seems like you want to calculate MACTH where Sum(Contribution) <> SUM(LIMIT), thats you can do in crystal by writing a formula.
Showing posts with label calculate. Show all posts
Showing posts with label calculate. Show all posts
Friday, February 24, 2012
Conditional calculation in SQL for CR
Conditional Average
SQL Server 2000
I have a table with an 'annual_inc' column, 7043 records.
I'd like to calculate an average on this column but I do not want to
include annual incomes below 20,000.
Any good way of doing that in a cursor or straight SQL?
Regards
Josh Francis
jwfrancis@.bbandt.comThis is a multi-part message in MIME format.
--=_NextPart_000_013C_01C36E1B.F12063F0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Try:
select
avg (annual_inc) as AvgIncome
from
MyTable
where
annual_inc >=3D 20000
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Joshua" <jfrancis@.bbandt.com> wrote in message =news:6407b746.0308290648.24f9962b@.posting.google.com...
SQL Server 2000
I have a table with an 'annual_inc' column, 7043 records.
I'd like to calculate an average on this column but I do not want to
include annual incomes below 20,000.
Any good way of doing that in a cursor or straight SQL?
Regards
Josh Francis
jwfrancis@.bbandt.com
--=_NextPart_000_013C_01C36E1B.F12063F0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1226" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#c0c0c0>
<DIV><FONT face=3DTahoma size=3D2>Try:</FONT></DIV>
<DIV><FONT face=3DTahoma size=3D2></FONT> </DIV>
<DIV><FONT face=3D"Courier New" size=3D2>select</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2> avg =(annual_inc) as AvgIncome</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>from</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2> =MyTable</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>where</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2> annual_inc =>=3D 20000</FONT></DIV>
<DIV><BR>-- <BR>Tom</DIV>
<DIV> </DIV>
<DIV>---<BR>T=homas A. Moreau, BSc, PhD, MCSE, MCDBA<BR>SQL Server MVP<BR>Columnist, SQL =Server Professional<BR>Toronto, ON Canada<BR><A href=3D"www.pinnaclepublishing.com=">http://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=
/sql</A></DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV>"Joshua" <<A href=3D"mailto:jfrancis@.bbandt.com">jfrancis@.bbandt.com</A>> wrote in =message <A href=3D"news:6407b746.0308290648.24f9962b@.posting.google.com">news:6407b7=46.0308290648.24f9962b@.posting.google.com</A>...</DIV>SQL Server 2000<BR><BR>I have a table with an 'annual_inc' column, 7043 records.<BR><BR>I'd like to calculate an average on this column but I do =not want to<BR>include annual incomes below 20,000.<BR><BR>Any good way of =doing that in a cursor or straight SQL?<BR><BR>Regards<BR><BR>Josh =Francis<BR><A href=3D"mailto:jwfrancis@.bbandt.com">jwfrancis@.bbandt.com</A></BODY></HTM=L>
--=_NextPart_000_013C_01C36E1B.F12063F0--|||From the details given this should work.
CREATE TABLE CalcAvg
(
ColToCalc int
)
INSERT CalcAvg VALUES(1000)
INSERT CalcAvg VALUES(2000)
INSERT CalcAvg VALUES(3000)
INSERT CalcAvg VALUES(1000)
select avg(ColToCalc) FROM CalcAvg
WHERE ColToCalc >1000
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
I have a table with an 'annual_inc' column, 7043 records.
I'd like to calculate an average on this column but I do not want to
include annual incomes below 20,000.
Any good way of doing that in a cursor or straight SQL?
Regards
Josh Francis
jwfrancis@.bbandt.comThis is a multi-part message in MIME format.
--=_NextPart_000_013C_01C36E1B.F12063F0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Try:
select
avg (annual_inc) as AvgIncome
from
MyTable
where
annual_inc >=3D 20000
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Joshua" <jfrancis@.bbandt.com> wrote in message =news:6407b746.0308290648.24f9962b@.posting.google.com...
SQL Server 2000
I have a table with an 'annual_inc' column, 7043 records.
I'd like to calculate an average on this column but I do not want to
include annual incomes below 20,000.
Any good way of doing that in a cursor or straight SQL?
Regards
Josh Francis
jwfrancis@.bbandt.com
--=_NextPart_000_013C_01C36E1B.F12063F0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1226" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#c0c0c0>
<DIV><FONT face=3DTahoma size=3D2>Try:</FONT></DIV>
<DIV><FONT face=3DTahoma size=3D2></FONT> </DIV>
<DIV><FONT face=3D"Courier New" size=3D2>select</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2> avg =(annual_inc) as AvgIncome</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>from</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2> =MyTable</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>where</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2> annual_inc =>=3D 20000</FONT></DIV>
<DIV><BR>-- <BR>Tom</DIV>
<DIV> </DIV>
<DIV>---<BR>T=homas A. Moreau, BSc, PhD, MCSE, MCDBA<BR>SQL Server MVP<BR>Columnist, SQL =Server Professional<BR>Toronto, ON Canada<BR><A href=3D"www.pinnaclepublishing.com=">http://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=
/sql</A></DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV>"Joshua" <<A href=3D"mailto:jfrancis@.bbandt.com">jfrancis@.bbandt.com</A>> wrote in =message <A href=3D"news:6407b746.0308290648.24f9962b@.posting.google.com">news:6407b7=46.0308290648.24f9962b@.posting.google.com</A>...</DIV>SQL Server 2000<BR><BR>I have a table with an 'annual_inc' column, 7043 records.<BR><BR>I'd like to calculate an average on this column but I do =not want to<BR>include annual incomes below 20,000.<BR><BR>Any good way of =doing that in a cursor or straight SQL?<BR><BR>Regards<BR><BR>Josh =Francis<BR><A href=3D"mailto:jwfrancis@.bbandt.com">jwfrancis@.bbandt.com</A></BODY></HTM=L>
--=_NextPart_000_013C_01C36E1B.F12063F0--|||From the details given this should work.
CREATE TABLE CalcAvg
(
ColToCalc int
)
INSERT CalcAvg VALUES(1000)
INSERT CalcAvg VALUES(2000)
INSERT CalcAvg VALUES(3000)
INSERT CalcAvg VALUES(1000)
select avg(ColToCalc) FROM CalcAvg
WHERE ColToCalc >1000
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
Subscribe to:
Posts (Atom)