Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

SUM function for subquery Expand / Collapse
Author
Message
Posted Tuesday, December 31, 2013 1:28 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 31, 2013 7:12 AM
Points: 17, Visits: 25
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


Post #1526680
Posted Tuesday, December 31, 2013 1:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 31, 2013 7:12 AM
Points: 17, Visits: 25
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

Post #1526681
Posted Tuesday, December 31, 2013 1:44 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, April 14, 2014 11:50 PM
Points: 1,867, Visits: 2,275
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/
Post #1526682
Posted Tuesday, December 31, 2013 1:49 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 31, 2013 7:12 AM
Points: 17, Visits: 25
Please ignore my second data structure post I thought the first didn't get posted.
Will try appending the table name now
Post #1526684
Posted Tuesday, December 31, 2013 2:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 31, 2013 7:12 AM
Points: 17, Visits: 25
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

Post #1526689
Posted Tuesday, December 31, 2013 2:52 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:33 AM
Points: 6,754, Visits: 12,854
Here's the very first query from your very first post, reformatted and with table aliases applied for readability:
SELECT 
SUM(Amount=ABS((
SELECT Top 1 Amount + VATAmount
FROM FAAccKitty k1 WITH(NOLOCK)
Where k1.PlaceID = w.PlaceID
AND k1.TransType = 'PZ'
)))

FROM FAAccWinners w

INNER JOIN Reservations r WITH(NOLOCK)
ON r.PlaceID = w.PlaceID

INNER JOIN Visitor v WITH(NOLOCK)
ON r.EMail = v.EMail

WHERE w.Comments_Approved = 'Yes'
AND v.VisitorID = 274

AND w.PlaceID IN (
SELECT PlaceID
FROM FAAccKitty k2 WITH(NOLOCK)
Where k2.PlaceID = w.PlaceID
AND k2.TransType ='PZ'
AND Year(k2.TransDate) > 1900
AND k2.Approved = 'Yes'
)

AND w.PlaceID NOT IN (
SELECT PlaceID
FROM FAAccKitty k3 WITH(NOLOCK)
Where k3.PlaceID = w.PlaceID
AND k3.TransType = 'PC'
)


There are several reasons why this is unlikely to return the result you are expecting to see. For instance, for each row returned by the main query, the subquery in the SELECT will be run. It has no ORDER BY to correspond to TOP so you don't know which row will be returned - and even if you know now, it could change with the addition or deletion of data from the table, or even by the addition of an index on a seemingly unrelated set of columns. The sum of a bunch of numbers you're not sure about is one number that you're fairly certain is unlikely to be correct.

You - and we - need to know more about your data. Sample scripts would help enormously (see "please read this" in my sig below), but there are shortcuts which might work to begin with.

Try this query:
-- test query 1
SELECT
w.PlaceID

FROM FAAccWinners w

INNER JOIN Reservations r WITH(NOLOCK)
ON r.PlaceID = w.PlaceID

INNER JOIN Visitor v WITH(NOLOCK)
ON r.EMail = v.EMail

WHERE w.Comments_Approved = 'Yes'
AND v.VisitorID = 274


It will return all of the PlaceID values in table FAAccWinners associated with VisitorID = 274. How many rows are returned? Is there only one value of PlaceID or several?

Then run this query:
-- test query 2
SELECT
k1.*

FROM FAAccWinners w

INNER JOIN Reservations r WITH(NOLOCK)
ON r.PlaceID = w.PlaceID

INNER JOIN Visitor v WITH(NOLOCK)
ON r.EMail = v.EMail

INNER JOIN FAAccKitty k1 WITH(NOLOCK)
ON k1.PlaceID = w.PlaceID

WHERE w.Comments_Approved = 'Yes'
AND v.VisitorID = 274

Now this query will tell you a ton of stuff:
How many rows in FAAccKitty per PlaceID
The distribution of the filter columns (TransType, TransDate, Approved) in FAAccKitty
Actual values of Amount and VATAmount.
You should be able to identify the rows you want to include in your aggregate, and sets (of common PlaceID) that you want to exclude.
You can derive from this the exact result you should obtain from any given value of VisitorID - which means you have numbers to match to whilst composing and testing your query.

Give it a whirl, see how you get on. It would be interesting for us to see the output of test query 2.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1526698
Posted Tuesday, December 31, 2013 3:07 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 31, 2013 7:12 AM
Points: 17, Visits: 25
Thank you I have got it working now with the CTE the ABS() function was missing.

I will however try to implement your other suggestions as they seem quite interesting

Thank you again
Post #1526704
Posted Tuesday, December 31, 2013 3:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:33 AM
Points: 6,754, Visits: 12,854
Can you post your solution please? It's closure for your thread, offers closure for those who have helped you, and may help others who stumble upon this thread whilst seeking answers to a similar problem.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1526710
Posted Tuesday, December 31, 2013 3:47 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 31, 2013 7:12 AM
Points: 17, Visits: 25
This is what worked for me. Thank you very much.

;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(ABS(TOTAL))
FROM CTE

Post #1526712
Posted Tuesday, December 31, 2013 4:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:33 AM
Points: 6,754, Visits: 12,854
You're welcome, thanks for posting the final query. If it works for you and generates the results you want, then that's good and you're probably done - but I'd recommend a little caution with it, if not some analysis and a bit of a rewrite. Coding by trial and error very often goes horribly wrong.

-- Since the CTE can only return one row, this query is exactly the same; 
SELECT Top 1
ABS(Amount + VATAmount) AS TOTAL

FROM FAAccKitty k WITH(NOLOCK)

INNER JOIN FAAccWinners w
on k.PlaceID = w.PlaceID

INNER JOIN Reservations r WITH(NOLOCK)
ON r.PlaceID = w.PlaceID

INNER JOIN Visitor v WITH(NOLOCK)
ON r.EMail = v.EMail

WHERE w.Comments_Approved = 'Yes'

AND w.PlaceID IN (
SELECT PlaceID
FROM FAAccKitty WITH(NOLOCK)
Where FAAccKitty.PlaceID = w.PlaceID
AND FAAccKitty.TransType = 'PZ'
AND Year(TransDate) > 1900
AND FAAccKitty.Approved = 'Yes'
)

AND w.PlaceID NOT IN (
SELECT PlaceID
FROM FAAccKitty WITH(NOLOCK)
Where FAAccKitty.PlaceID = w.PlaceID
AND TransType='PC'
)

AND v.VisitorID = 274

AND k.TransType = 'PZ'



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1526716
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse