December 31, 2013 at 12:21 am
Hi
I'm trying to get a total for amounts that will come from a subquery.
I'm getting multiple errors and I've tried multiple places for the SUM function but
I'm quite lost now.
Thank you
My code:
SELECT SUM(Amount=ABS((SELECT Top 1 Amount+VATAmount
FROM FAAccKitty WITH(NOLOCK) Where FAAccKitty.PlaceID=FAAccWinners.PlaceID AND FAAccKitty.TransType='PZ')))
FROM FAAccWinners
INNER JOIN Reservations WITH(NOLOCK) ON (Reservations.PlaceID = FAAccWinners.PlaceID)
INNER JOIN Visitor WITH(NOLOCK) ON (Reservations.EMail = Visitor.EMail)
WHERE FAAccWinners.Comments_Approved='Yes'
AND FAAccWinners.PlaceID IN (SELECT PlaceID FROM FAAccKitty WITH(NOLOCK) Where FAAccKitty.PlaceID=FAAccWinners.PlaceID
AND FAAccKitty.TransType='PZ' AND Year(TransDate)>1900 AND FAAccKitty.Approved='Yes') AND FAAccWinners.PlaceID NOT IN
(SELECT PlaceID FROM FAAccKitty WITH(NOLOCK) Where FAAccKitty.PlaceID=FAAccWinners.PlaceID AND TransType='PC')
AND Visitor.VisitorID= 274
Also tried:
SELECT SUM(ABS((SELECT Top 1 Amount+VATAmount
FROM FAAccKitty WITH(NOLOCK) Where FAAccKitty.PlaceID=FAAccWinners.PlaceID AND FAAccKitty.TransType='PZ')))
FROM FAAccWinners
INNER JOIN Reservations WITH(NOLOCK) ON (Reservations.PlaceID = FAAccWinners.PlaceID)
INNER JOIN Visitor WITH(NOLOCK) ON (Reservations.EMail = Visitor.EMail)
WHERE FAAccWinners.Comments_Approved='Yes'
AND FAAccWinners.PlaceID IN (SELECT PlaceID FROM FAAccKitty WITH(NOLOCK) Where FAAccKitty.PlaceID=FAAccWinners.PlaceID
AND FAAccKitty.TransType='PZ' AND Year(TransDate)>1900 AND FAAccKitty.Approved='Yes') AND FAAccWinners.PlaceID NOT IN
(SELECT PlaceID FROM FAAccKitty WITH(NOLOCK) Where FAAccKitty.PlaceID=FAAccWinners.PlaceID AND TransType='PC')
AND Visitor.VisitorID= 274
Tried this as well:
SELECT Amount=ABS((SUM(SELECT Top 1 Amount+VATAmount
FROM FAAccKitty WITH(NOLOCK) Where FAAccKitty.PlaceID=FAAccWinners.PlaceID AND FAAccKitty.TransType='PZ')))
FROM FAAccWinners
INNER JOIN Reservations WITH(NOLOCK) ON (Reservations.PlaceID = FAAccWinners.PlaceID)
INNER JOIN Visitor WITH(NOLOCK) ON (Reservations.EMail = Visitor.EMail)
WHERE FAAccWinners.Comments_Approved='Yes'
AND FAAccWinners.PlaceID IN (SELECT PlaceID FROM FAAccKitty WITH(NOLOCK) Where FAAccKitty.PlaceID=FAAccWinners.PlaceID
AND FAAccKitty.TransType='PZ' AND Year(TransDate)>1900 AND FAAccKitty.Approved='Yes') AND FAAccWinners.PlaceID NOT IN
(SELECT PlaceID FROM FAAccKitty WITH(NOLOCK) Where FAAccKitty.PlaceID=FAAccWinners.PlaceID AND TransType='PC')
AND Visitor.VisitorID= 274
Any help will be greatly appreciated
December 31, 2013 at 12:24 am
Hi,
If you post table structure with some dummy data and desired output it will be easy for us to help you....
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
December 31, 2013 at 12:24 am
What error you get?
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
December 31, 2013 at 12:28 am
Just a Hit and Trial....
;WITH CTE AS
(
SELECT Top 1 Amount+VATAmount as Total
FROM FAAccKitty WITH(NOLOCK) Where FAAccKitty.PlaceID=FAAccWinners.PlaceID AND FAAccKitty.TransType='PZ')))
FROM FAAccWinners
INNER JOIN Reservations WITH(NOLOCK) ON (Reservations.PlaceID = FAAccWinners.PlaceID)
INNER JOIN Visitor WITH(NOLOCK) ON (Reservations.EMail = Visitor.EMail)
WHERE FAAccWinners.Comments_Approved='Yes'
AND FAAccWinners.PlaceID IN (SELECT PlaceID FROM FAAccKitty WITH(NOLOCK) Where FAAccKitty.PlaceID=FAAccWinners.PlaceID
AND FAAccKitty.TransType='PZ' AND Year(TransDate)>1900 AND FAAccKitty.Approved='Yes') AND FAAccWinners.PlaceID NOT IN
(SELECT PlaceID FROM FAAccKitty WITH(NOLOCK) Where FAAccKitty.PlaceID=FAAccWinners.PlaceID AND TransType='PC')
AND Visitor.VisitorID= 274
)
SELECT SUM(Total)
FROM CTE
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
December 31, 2013 at 12:35 am
Getting these
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '='.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ')'.
Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'AND'.
Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'AND'
also
December 31, 2013 at 12:42 am
Getting syntax errors for this
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ')'.
Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'AND'.
Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'AND'.
December 31, 2013 at 12:44 am
BlackIceAngel (12/31/2013)
Getting syntax errors for thisMsg 102, Level 15, State 1, Line 5
Incorrect syntax near ')'.
Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'AND'.
Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'AND'.
Can you please post the ddl scripts of tables that you are using in your query
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
December 31, 2013 at 12:47 am
The desired output is all the amounts added up to give one value
Without the SUM function I just have like 9 records for example like this
2400.00
2640.00
1900.00
478.80
750.00
600.00
1030.00
980.00
425.00
I just want these added together to give me their SUM.
If that is not enough to go or do you still require the data table structures and dummy data?
Thank you
December 31, 2013 at 12:56 am
Try this,
this is what i can do without table structure:
;WITH CTE AS
(SELECT Top 1 Amount+VATAmount AS TOTAL
FROM FAAccKitty WITH(NOLOCK)
inner JOIN FAAccWinners on FAAccKitty.PlaceID=FAAccWinners.PlaceID
INNER JOIN Reservations WITH(NOLOCK) ON (Reservations.PlaceID = FAAccWinners.PlaceID)
INNER JOIN Visitor WITH(NOLOCK) ON (Reservations.EMail = Visitor.EMail)
WHERE FAAccWinners.Comments_Approved='Yes'
AND FAAccWinners.PlaceID IN (SELECT PlaceID FROM FAAccKitty WITH(NOLOCK) Where FAAccKitty.PlaceID=FAAccWinners.PlaceID
AND FAAccKitty.TransType='PZ' AND Year(TransDate)>1900 AND FAAccKitty.Approved='Yes') AND FAAccWinners.PlaceID NOT IN
(SELECT PlaceID FROM FAAccKitty WITH(NOLOCK) Where FAAccKitty.PlaceID=FAAccWinners.PlaceID AND TransType='PC')
AND Visitor.VisitorID= 274
AND FAAccKitty.TransType='PZ'
)
SELECT SUM(TOTAL)
FROM CTE
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
December 31, 2013 at 1:09 am
This works if I take out the TOP 1. I'm getting the right total value but it comes out as a negative?
The Amount and VatAmount fields are both set to data type money and not null but I don't see how that could
give me a negative.
December 31, 2013 at 1:28 am
As I said I'm getting negative value for total but I don't have any negative values in
the dataset.
I've been able to get the data table structure if that helps.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[FAAKitty](
[KittyID] [int] IDENTITY(1,1) NOT NULL,
[CDate] [smalldatetime] NOT NULL,
[PlaceID] [int] NOT NULL,
[TransType] [varchar](5) NOT NULL,
[Amount] [money] NOT NULL,
[VATAmount] [money] NOT NULL,
[TransDate] [smalldatetime] NOT NULL,
[KittyBalance] [money] NOT NULL,
CONSTRAINT [PK_FAAccKitty] PRIMARY KEY CLUSTERED
(
[KittyID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Reservations(
[PlaceID] [int] IDENTITY(1,1) NOT NULL,
[MemberID] [int] NOT NULL,
[EMail] [varchar](70) NOT NULL,
[PDate] [datetime] NULL,
[Amount] [money] NULL,
[ADate] [datetime] NULL,
[DDDate] [datetime] NULL,
[PEDate] [datetime] NULL,
[CCDate] [datetime] NULL,
[PEAmount] [money] NULL,
[CancellationPolicy] [text] NULL,
[AgentID] [int] NULL,
[RCommission] [money] NULL,
[PRDate] [datetime] NULL,
[PADate] [datetime] NULL,
[Paygate_Ref] [varchar](100) NULL,
[Paygate_Date] [smalldatetime] NOT NULL,
[Bank_Ref] [varchar](100) NULL,
[Bank_Date] [smalldatetime] NOT NULL,
[Comments] [text] NULL,
CONSTRAINT [PK_Bookings] PRIMARY KEY CLUSTERED
(
[PlaceID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[FAAccWinners](
[WinnerID] [int] IDENTITY(1,1) NOT NULL,
[PlaceID] [int] NOT NULL,
[CDate] [smalldatetime] NOT NULL,
[BDReqDate] [smalldatetime] NOT NULL,
[BranchCode] [varchar](50) NULL,
[Pic] [varchar](50) NULL,
[GuestComment] [text] NULL,
[UpdateDate] [smalldatetime] NOT NULL,
[PaymentType] [varchar](5) NULL,
[Comments_Approved] [varchar](5) NULL,
[Comments_ApprovedBy] [varchar](50) NULL,
CONSTRAINT [PK_FAAccWinners] PRIMARY KEY CLUSTERED
(
[WinnerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Visitor](
[VisitorID] [int] IDENTITY(1,1) NOT NULL,
[EMail] [varchar](70) NOT NULL,
[FirstName] [varchar](50) NULL,
[Surname] [varchar](50) NULL,
[Password] [varchar](20) NULL,
CONSTRAINT [PK_Visitor] PRIMARY KEY NONCLUSTERED
(
[VisitorID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY],
UNIQUE NONCLUSTERED
(
[EMail] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
December 31, 2013 at 1:42 am
Not sure why I'm getting the negative SUM total because there are no
negatives in the dataset.
I've been able to get the data structure as requested if that helps
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[FAAKitty](
[KittyID] [int] IDENTITY(1,1) NOT NULL,
[CDate] [smalldatetime] NOT NULL,
[PlaceID] [int] NOT NULL,
[TransType] [varchar](5) NOT NULL,
[Amount] [money] NOT NULL,
[VATAmount] [money] NOT NULL,
[TransDate] [smalldatetime] NOT NULL,
[KittyBalance] [money] NOT NULL,
CONSTRAINT [PK_FAAccKitty] PRIMARY KEY CLUSTERED
(
[KittyID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Reservations(
[PlaceID] [int] IDENTITY(1,1) NOT NULL,
[MemberID] [int] NOT NULL,
[EMail] [varchar](70) NOT NULL,
[PDate] [datetime] NULL,
[Amount] [money] NULL,
[ADate] [datetime] NULL,
[DDDate] [datetime] NULL,
[PEDate] [datetime] NULL,
[CCDate] [datetime] NULL,
[PEAmount] [money] NULL,
[CancellationPolicy] [text] NULL,
[AgentID] [int] NULL,
[RCommission] [money] NULL,
[PRDate] [datetime] NULL,
[PADate] [datetime] NULL,
[Paygate_Ref] [varchar](100) NULL,
[Paygate_Date] [smalldatetime] NOT NULL,
[Bank_Ref] [varchar](100) NULL,
[Bank_Date] [smalldatetime] NOT NULL,
[Comments] [text] NULL,
CONSTRAINT [PK_Bookings] PRIMARY KEY CLUSTERED
(
[PlaceID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[FAAccWinners](
[WinnerID] [int] IDENTITY(1,1) NOT NULL,
[PlaceID] [int] NOT NULL,
[CDate] [smalldatetime] NOT NULL,
[BDReqDate] [smalldatetime] NOT NULL,
[BranchCode] [varchar](50) NULL,
[Pic] [varchar](50) NULL,
[GuestComment] [text] NULL,
[UpdateDate] [smalldatetime] NOT NULL,
[PaymentType] [varchar](5) NULL,
[Comments_Approved] [varchar](5) NULL,
[Comments_ApprovedBy] [varchar](50) NULL,
CONSTRAINT [PK_FAAccWinners] PRIMARY KEY CLUSTERED
(
[WinnerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Visitor](
[VisitorID] [int] IDENTITY(1,1) NOT NULL,
[EMail] [varchar](70) NOT NULL,
[FirstName] [varchar](50) NULL,
[Surname] [varchar](50) NULL,
[Password] [varchar](20) NULL,
CONSTRAINT [PK_Visitor] PRIMARY KEY NONCLUSTERED
(
[VisitorID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY],
UNIQUE NONCLUSTERED
(
[EMail] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
December 31, 2013 at 1:44 am
Amount column in query is present in two tables....
From which you are extracting the amount?
May be one your table data contains negative values...
Place table name with Amount in select query and check if it still gives you negative value...
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
December 31, 2013 at 1:49 am
Please ignore my second data structure post I thought the first didn't get posted.
Will try appending the table name now
December 31, 2013 at 2:00 am
Appending the table name didn't work the strange thing is though if I use
just the SELECT from your code I get all my previous values coming as negative.
SELECT Amount+VATAmount AS TOTAL
FROM FAAccKitty WITH(NOLOCK)
inner JOIN FAAccWinners on FAAccKitty.PlaceID=FAAccWinners.PlaceID
INNER JOIN Reservations WITH(NOLOCK) ON (Reservations.PlaceID = FAAccWinners.PlaceID)
INNER JOIN Visitor WITH(NOLOCK) ON (Reservations.EMail = Visitor.EMail)
WHERE FAAccWinners.Comments_Approved='Yes'
AND FAAccWinners.PlaceID IN (SELECT PlaceID FROM FAAccKitty WITH(NOLOCK) Where FAAccKitty.PlaceID=FAAccWinners.PlaceID
AND FAAccKitty.TransType='PZ' AND Year(TransDate)>1900 AND FAAccKitty.Approved='Yes') AND FAAccWinners.PlaceID NOT IN
(SELECT PlaceID FROM FAAccKitty WITH(NOLOCK) Where FAAccKitty.PlaceID=FAAccWinners.PlaceID AND TransType='PC')
AND Visitor.VisitorID= 274 AND FAAccKitty.TransType='PZ'
This code I originally posted, with just my select, gives the positive values I'm trying to add if that helps
SELECT Amount=ABS((SELECT Top 1 Amount+VATAmount
FROM FAAccKitty WITH(NOLOCK) Where FAAccKitty.PlaceID=FAAccWinners.PlaceID AND FAAccKitty.TransType='PZ'))
FROM FAAccWinners
INNER JOIN Reservations WITH(NOLOCK) ON (Reservations.PlaceID = FAAccWinners.PlaceID)
INNER JOIN Visitor WITH(NOLOCK) ON (Reservations.EMail = Visitor.EMail)
WHERE FAAccWinners.Comments_Approved='Yes'
AND FAAccWinners.PlaceID IN (SELECT PlaceID FROM FAAccKitty WITH(NOLOCK) Where FAAccKitty.PlaceID=FAAccWinners.PlaceID
AND FAAccKitty.TransType='PZ' AND Year(TransDate)>1900 AND FAAccKitty.Approved='Yes') AND FAAccWinners.PlaceID NOT IN
(SELECT PlaceID FROM FAAccKitty WITH(NOLOCK) Where FAAccKitty.PlaceID=FAAccWinners.PlaceID AND TransType='PC')
AND Visitor.VisitorID= 274
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply