SQL Related Articles: Sum Calculations in select statement and filtering based on value
2003-02-05 -- Andy Stapleton
Newsgroups: TopSpeed.Products.Drivers
Let me take a stab at it...
SELECT
F.PayerID,
CAST(F.LastName + ', ' + F.FirstName AS VARCHAR(52)) AS 'Family
Name',
SUM(CASE WHEN L.TType = 'Charge' THEN L.Amount END) AS Charges,
SUM(CASE WHEN L.TType = 'Credit' THEN L.Amount END) AS Credits,
SUM(CASE WHEN L.TType = 'Payment' THEN L.Amount END) AS Payments,
SUM(CASE WHEN L.TType = 'Charge' THEN L.Amount WHEN L.TType =
'Credit' THEN 0 - L.Amount WHEN L.TType = 'Payment' THEN 0 - L.Amount END)
AS BalanceDue
FROM Ledger AS L, Family AS F
WHERE F.PayerID = L.PayerID
GROUP BY F.LastName, F.FirstName, F.PayerID
HAVING
SUM(CASE WHEN L.TType = 'Charge' THEN L.Amount
WHEN L.TType = 'Credit' THEN 0 - L.Amount WHEN L.TType =
'Payment' THEN 0 - L.Amount
END) > 10.00
Have you tried the above?
--
Andy <> Stapleton
CIO DB|Wired.com / (ccs) Cowboy Computing Solutions
www.dbwired.com Web and Clarionet hosting
www.ccscowboy.com CCS SQL templates
www.Paywire.com ACH / Credit Card payment processing
"Ben Kim (Vertical Dimensions, Inc.)" wrote in
message news:3e4147d8$1@news.softvelocity.com...
> Hello all,
>
> I am trying to program a sql-list that shows:
>
> Payer ID Family Name Charges Credits Payments Balance
Due
>
> Every thing works as expected with one exception. If the user only wants
to
> see all balances over lets say $10, how do I build that into the statement
> below without getting an error: ERROR CONVERTING DATA TYPE VARCHAR TO
> NUMERIC
>
> SQL Statement:
> --------------------------------------------------------------------------
--
> ---
> SELECT
> F.PayerID,
> CAST(F.LastName + ', ' + F.FirstName AS VARCHAR(52)) AS 'Family
> Name',
> SUM(CASE WHEN L.TType = 'Charge' THEN L.Amount END) AS Charges,
> SUM(CASE WHEN L.TType = 'Credit' THEN L.Amount END) AS Credits,
> SUM(CASE WHEN L.TType = 'Payment' THEN L.Amount END) AS Payments,
> SUM(CASE WHEN L.TType = 'Charge' THEN L.Amount WHEN L.TType =
> 'Credit' THEN 0 - L.Amount WHEN L.TType = 'Payment' THEN 0 - L.Amount END)
> AS 'BalanceDue'
> FROM Ledger AS L, Family AS F
> WHERE F.PayerID = L.PayerID
> GROUP BY F.LastName, F.FirstName, F.PayerID
> HAVING CAST('BalanceDue' AS DECIMAL(19, 2)) >
> 10.00 <---tried BD > 10.00, CONVERT(DECIMAL...)
> --------------------------------------------------------------------------
--
> -
>
> I have tried CAST and CONVERT and nothing at all but HAVING 'BalanceDue' >
> 10.00 but get the same error. If I remove the HAVING clause it works like
a
> champ.
>
> Thanks Ahead Of Time!
>
> Ben Kim
> Using C5.5EE Latest Patches (ABC/Legacy 32-bit)
> MS-SQL 2K Enterprise Edition
> Windows XP Pro
>
>
Printed May 1, 2024, 2:20 pm
This article has been viewed/printed 35119 times.
Google search
has resulted in 3112 hits on this article since January 25, 2004.