Calculating SUM if column = 1

[Drew]

I have the following SP that I am trying to use to report with,

CREATE PROCEDURE spCreditCardLog
@UserName varchar(30)
AS
--Get all records by username that is logged in
SELECT UID, C.CardholderID, CardholderName, Department, CardNumber,
CardType, PONum, PODate, Vendor, Description, Amount, CCCObjectCode,
Complete, Carryover
FROM CreditCardLog C INNER JOIN Cardholder CH ON C.CardholderID =
CH.CardholderID INNER JOIN CardType CT ON CH.CardTypeID = CT.CardTypeID
WHERE C.CardholderID = @UserName
ORDER BY C.PONum;
GO

On my report, I need to calculate the SUM of Amount where Carryover = 1. Is
this possible?

Thanks,
Drew Laing

If needed, here is my SQL Server table structure,

CREATE TABLE [dbo].[CreditCardLog] (
[UID] [int] IDENTITY (1, 1) NOT NULL ,
[CardholderID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PONum] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PODate] [datetime] NULL ,
[Vendor] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Description] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Amount] [money] NULL ,
[CCCObjectCode] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Complete] [bit] NULL ,
[Carryover] [bit] NULL
) ON [PRIMARY]
GO

 

[Duane Hookom]

As per my reply in another forum, you can try the following. There is no
Sum() because ther is no GROUP BY. The CarryOverAmount will only contain
amounts where CarryOver = 1.

CREATE PROCEDURE spCreditCardLog
@UserName varchar(30)
AS
--Get all records by username that is logged in
SELECT UID, C.CardholderID, CardholderName, Department, CardNumber,
CardType, PONum, PODate, Vendor, Description, Amount, CCCObjectCode,
Complete, Carryover,
CASE WHEN CarryOver=1 THEN Amount ELSE 0 END as CarryOverAmount
FROM CreditCardLog C INNER JOIN Cardholder CH ON C.CardholderID =
CH.CardholderID INNER JOIN CardType CT ON CH.CardTypeID = CT.CardTypeID
WHERE C.CardholderID = @UserName
ORDER BY C.PONum;
GO

 

[Drew]

Just getting ready to post the fix you shared with me... sorry about
cross-posting, I just wanted to try to get as many ideas as possible.

Thanks,
Drew

As per my reply in another forum, you can try the following. There is no
Sum() because ther is no GROUP BY. The CarryOverAmount will only contain
amounts where CarryOver = 1.

CREATE PROCEDURE spCreditCardLog
@UserName varchar(30)
AS
--Get all records by username that is logged in
SELECT UID, C.CardholderID, CardholderName, Department, CardNumber,
CardType, PONum, PODate, Vendor, Description, Amount, CCCObjectCode,
Complete, Carryover,
CASE WHEN CarryOver=1 THEN Amount ELSE 0 END as CarryOverAmount
FROM CreditCardLog C INNER JOIN Cardholder CH ON C.CardholderID =
CH.CardholderID INNER JOIN CardType CT ON CH.CardTypeID = CT.CardTypeID
WHERE C.CardholderID = @UserName
ORDER BY C.PONum;
GO


--
Duane Hookom
MS Access MVP
--

I have the following SP that I am trying to use to report with,

CREATE PROCEDURE spCreditCardLog
@UserName varchar(30)
AS
--Get all records by username that is logged in
SELECT UID, C.CardholderID, CardholderName, Department, CardNumber,
CardType, PONum, PODate, Vendor, Description, Amount, CCCObjectCode,
Complete, Carryover
FROM CreditCardLog C INNER JOIN Cardholder CH ON C.CardholderID =
CH.CardholderID INNER JOIN CardType CT ON CH.CardTypeID = CT.CardTypeID
WHERE C.CardholderID = @UserName
ORDER BY C.PONum;
GO

On my report, I need to calculate the SUM of Amount where Carryover = 1.
Is this possible?

Thanks,
Drew Laing

If needed, here is my SQL Server table structure,

CREATE TABLE [dbo].[CreditCardLog] (
[UID] [int] IDENTITY (1, 1) NOT NULL ,
[CardholderID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PONum] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PODate] [datetime] NULL ,
[Vendor] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Description] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Amount] [money] NULL ,
[CCCObjectCode] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Complete] [bit] NULL ,
[Carryover] [bit] NULL
) ON [PRIMARY]
GO