selecting sum not equal to - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

selecting sum not equal to

Author

Topic

jwigg5pt0
Starting Member

8 Posts

Posted - 2012-04-23 : 18:59:42

Table 1 - AppointmentTable with column id(primay) and appointmentTotal(decimal)

Table 2 - TotalsTable with columns appointmentID and total(decimal)

A total is submitted to the TotalsTable via asp.net app that includes the appointmentID. Each time a total is submitted, the app will calculate the sum of all the collected totals in the TotalsTable with the appropriate appointmentID and update the appointmentTotal column in the AppointmentTable with that sum.

What I am trying to accomplish is to find any mismatches where the sum in the TotalsTable for an appointmentID DOES NOT EQUAL the appointmentTotal in the AppointmentTable

A few select queries below that I have tried are not working properly. Any suggestions??

SELECT id
FROM AppointmentTable
WHERE appointmentTotal !=
(SELECT SUM(total)
FROM TotalsTable
WHERE TotalsTable.appointmentID = AppointmentTable.id)

----This will pull up a result only if the SUM is not null and does not equal the appointmentTotal, not vica verca. SO if there weren't any entries in the TotalsTable and the appointmentTotal in the AppointmentsTable ='s any decimal > 0, this does not return. This only will return if there was an entry in the TotalsTable that didnt SUM up to what was in the AppointmentTable

---AND ANOTHER---
SELECT DISTINCT(appointmentID)
, SUM(total) as total
FROM TotalsTable
WHERE total !=
(SELECT appointmentTotal
FROM AppointmentTable
WHERE TotalsTable.appointmentTotal= AppointmentTable.id)
GROUP BY appointmentID
--This one is all over the place. What it looks like is it is showing results if the LAST entry in the TotalsTable does not equal whats in the AppointmentTable although the SUM is correct when running a single select and sum query to verify what it should be.

if there are any misspellings in the tables or names showing above, its not the issue (table and column names were obviously changed on the post)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-23 : 19:10:38

[code]
SELECT *
FROM AppointmentTable a
INNER JOIN (SELECT appointmentID ,SUM(Total) AS Totals
FROM TotalsTable
GROUP BY appointmentID)t
ON t.appointmentID = a.appointmentID
WHERE Totals <> appointmentTotal
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-23 : 19:14:26

[code]SELECT
COALESCE(a.id,t.AppointmentId) AS Id,
COALESCE(a.AppointmentTotal,0) AS AppointmentTotal,
COALESCE(SUM(t.total),0) AS TotalsTotal
FROM
Appointments a
FULL JOIN Totals t ON
t.AppointmentId = a.id
GROUP BY
COALESCE(a.AppointmentTotal,0),
COALESCE(a.id,t.AppointmentId)
HAVING
COALESCE(a.AppointmentTotal,0) <> COALESCE(SUM(t.total),0)[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-23 : 19:21:37

for including any entries not present in totalstable use


SELECT *
FROM AppointmentTable a 
LEFT JOIN (SELECT appointmentID ,SUM(Total) AS Totals
            FROM TotalsTable 
            GROUP BY appointmentID)t
ON t.appointmentID = a.appointmentID 
WHERE COALESCE(Totals,0) <> appointmentTotal

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jwigg5pt0
Starting Member

8 Posts

Posted - 2012-04-23 : 20:38:52

quote:
Originally posted by visakh16

SELECT *
FROM AppointmentTable a
INNER JOIN (SELECT appointmentID ,SUM(Total) AS Totals
FROM TotalsTable
GROUP BY appointmentID)t
ON t.appointmentID = a.appointmentID
WHERE Totals <> appointmentTotal

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

This here is doing the same as the first one of mine did. As a test there is a value in the AppointmentTable.appointmentTotal but no entries in TotalsTable with the given appointmentID therefore returning a null sum value, which does not pull as a record running this query

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-23 : 20:41:00

quote:
Originally posted by jwigg5pt0

quote:
Originally posted by visakh16

SELECT *
FROM AppointmentTable a
INNER JOIN (SELECT appointmentID ,SUM(Total) AS Totals
FROM TotalsTable
GROUP BY appointmentID)t
ON t.appointmentID = a.appointmentID
WHERE Totals <> appointmentTotal

use the modified suggestion with left join
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

This here is doing the same as the first one of mine did. As a test there is a value in the AppointmentTable.appointmentTotal but no entries in TotalsTable with the given appointmentID therefore returning a null sum value, which does not pull as a record running this query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jwigg5pt0
Starting Member

8 Posts

Posted - 2012-04-23 : 20:44:00

quote:
Originally posted by visakh16

for including any entries not present in totalstable use

SELECT *
FROM AppointmentTable a
LEFT JOIN (SELECT appointmentID ,SUM(Total) AS Totals
FROM TotalsTable
GROUP BY appointmentID)t
ON t.appointmentID = a.appointmentID
WHERE COALESCE(Totals,0) <> appointmentTotal

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

This here worked both ways. Thank you.
Although why am I getting an error -- The multi-part identifier "AppointmentTable.id" could not be bound. -- when trying to select an individual column (AppointmentTable.id) instead of all (*) in the query?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-23 : 21:25:02

which column it tried? it works for all columns in AppointmentTable

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources