Calculating SUM if column = 1

  • Thread starter Thread starter Drew
  • Start date Start date
D

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
 
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
 
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

Duane Hookom said:
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
--

Drew said:
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
Click to expand...
Click to expand...
 
Back
Top