Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 275
  • Last Modified:

How to replicate the effect of using an aggregate function in where clause?

Hello experts!

I've been struggling with this for a couple of hours now, so I felt it was time to post!

I'm self learning SQL over the years, and have a grasp of the basics, but it gets to a bit of trial and error (sometimes mostly error!) when it comes to advanced aspects!

I have three tables, customers, orders and transactions.

I want to return a report of customers where the total order value and total transaction value do not match - e.g. they have returned an item, but have not been given their refund.

This is the first query I tried, which didn't work since, as I learnt this morning, I can't use aggregate functions in the where clause.

SELECT cId, SUM(oTotal) AS vordttl, SUM(tValue) AS vtranttl 
FROM (Customers LEFT JOIN Orders ON Customers.cId = Orders.oCust) LEFT JOIN Transactions ON Customers.cId = Transactions.tCust
WHERE SUM(oTotal) <> SUM(tValue)
GROUP BY cId

Open in new window


Since then I've tried a whole host of ways round, which haven't worked...I've chosen to show the option above as whlist I know it's wrong, to human logic I think it expresses most clearly what I am trying to do.

Can someone help me achieve the result I'm looking for?

Thanks,

Dan
0
stressfreewebs
Asked:
stressfreewebs
  • 5
  • 3
1 Solution
 
Om PrakashCommented:
But you can use aggregate functions in the having clause. example:

SELECT
      cId,
      SUM(oTotal) AS vordttl,
      SUM(tValue) AS vtranttl
FROM (Customers LEFT JOIN Orders ON Customers.cId = Orders.oCust) LEFT JOIN Transactions ON Customers.cId = Transactions.tCust
GROUP BY cId
having SUM(oTotal) <> SUM(tValue)
0
 
stressfreewebsAuthor Commented:
That has done the trick for the SQL...the numbers don't add up though...can't work out why...e.g. if I run just "SELECT SUM(oTotal) AS vordttl WHERE oCust = xxx" to get the order total for a single cust, I get a totally different result to the one this query gets...am I using the wrong sort of join and it's picking multiple records perhaps?
0
 
Om PrakashCommented:
Try the following query, although i don't have the column level details, i think you need to add additional condition in join and use inner join instead of left join...

SELECT
      cId,
      SUM(oTotal) AS vordttl,
      SUM(tValue) AS vtranttl
FROM Customers
JOIN Orders ON Customers.cId = Orders.oCust
JOIN Transactions ON Customers.cId = Transactions.tCust and Orders.orderid = Transactions.orderid
GROUP BY cId
having SUM(oTotal) <> SUM(tValue)
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
stressfreewebsAuthor Commented:
I've tried this, substituing the correct column names thus:

SELECT cId, SUM(oTotal) AS vordttl, SUM(tValue) AS vtranttl FROM Customers JOIN Orders ON Customers.cId = Orders.oCust JOIN Transactions ON Customers.cId = Transactions.tCust AND Orders.oId = Transactions.tOrder GROUP BY cId having SUM(oTotal) <> SUM(tValue)

But I now get :

"Microsoft JET Database Engine error '80040e14'

Syntax error in FROM clause. "

Have tried putting the brackets back in, no luck there either. Is there any other information I can give you which would help?

Thanks,

Dan
0
 
Om PrakashCommented:
try:
SELECT
cId, SUM(oTotal) AS vordttl, SUM(tValue) AS vtranttl FROM
Customers
inner JOIN Orders ON Customers.cId = Orders.oCust
inner JOIN Transactions ON Customers.cId = Transactions.tCust AND Orders.oId = Transactions.tOrder
GROUP BY cId having SUM(oTotal) <> SUM(tValue)
0
 
stressfreewebsAuthor Commented:
Thanks for sticking with me! I'm now getting:

Syntax error (missing operator) in query expression 'Customers.cId = Orders.oCust inner JOIN Transactions ON Customers.cId = Transactions.tCust'.

I'm going to have a go with trying to work out where this is coming from but thought I'd post here in the meantime!

0
 
stressfreewebsAuthor Commented:
I added in ( ) around the first join, and seems to be working now...I'm just going to check through some of the numbers :)

0
 
stressfreewebsAuthor Commented:
Thanks for all your help - this cracked it!

Dan
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now