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

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.

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