0

I have a small issue related to joining two tables based on the result of SUM().Following are my tables.

Customers Table  

CUSTOMERID      NAME AGE   ADDRESS       PHONE
        1        AA  33    Some Address  123654789
        2        BB  35    Some Address  123654789
        3        CC  55    Some Address  987654789
        4        DD  55    Some Address  987654789
        5        EE  25    Some Address  365754789
        6        FF  20    Some Address  365754789


Orders Table 

    ORDERID  CUSTOMERID ORDERAMOUNT
    C12335    3         50000
    F12336    3         12000
    C12337    3         5000
    C12338    1         3700
    C12339    2         1100

I display the customerID,Name,Address,Sum of orders of the customers whose Sum of ORDERAMOUNT is more than 50000.

However,since I cannot use an Aggregate function like SUM() after a WHERE clause,I am in trouble here.

SELECT C.NAME,C.ADDRESS,O.CUSTOMERID
FROM CUSTOMERS C
INNER JOIN
(
    SELECT CUSTOMERID,SUM(ORDERAMOUNT) AS sumorders FROM ORDERS GROUP BY CUSTOMERID
)O ON C.CUSTOMERID = O.CUSTOMERID;

The most progressive working query I could write was this and it does not evaluate the condition of Sum of ORDERAMOUNT is more than 50000.

Is there a way that I can evaluate that condition as well? A help is much appreciated. Thanks in advance :)

  • 1
    You cannot use SUM in a WHERE clause. However, you can use it in a HAVING. SELECT CUSTOMERID,SUM(ORDERAMOUNT) AS sumorders FROM ORDERS GROUP BY CUSTOMERID HAVING SUM(ORDERAMOUNT)>5000 – indiri Sep 21 '17 at 15:40
  • 1
    just filter the subquery with a having clause: – Renato Afonso Sep 21 '17 at 15:40
2

You cannot use SUM in a WHERE clause. However, you can use it in a HAVING

SELECT C.NAME,C.ADDRESS,O.CUSTOMERID
FROM CUSTOMERS C
INNER JOIN
(
    SELECT CUSTOMERID,SUM(ORDERAMOUNT) AS sumorders 
    FROM ORDERS 
    GROUP BY CUSTOMERID 
    HAVING SUM(ORDERAMOUNT)>5000
)O ON C.CUSTOMERID = O.CUSTOMERID;
|improve this answer|||||
1

Using "Having"

DECLARE  @Customers TABLE
(
customerid int,
name varchar(30),
age int,
address varchar(100),
phone varchar(100)
)


DECLARE @Orders TABLE
(
orderid varchar(10),
customerid int,
orderamount decimal(18,2)
)

INSERT INTO @Customers
(
    customerid,
    name,
    age,
    address,
    phone
)
VALUES

        (1,        'AA',  33,    'Some Address',  '123654789'),
        (2,        'BB',  35,    'Some Address',  '123654789'),
        (3,        'CC',  55,    'Some Address',  '987654789'),
        (4,        'DD',  55,    'Some Address',  '987654789'),
        (5,        'EE',  25,    'Some Address',  '365754789'),
        (6,        'FF',  20,    'Some Address',  '365754789')

INSERT INTO @Orders
(
    orderid,
    customerid,
    orderamount
)
VALUES
    ('C12335',    3,         50000),
    ('F12336',    3,         12000),
    ('C12337',    3,         5000 ),
    ('C12338',    1,         3700 ),
    ('C12339',    2,         1100 )




SELECT c.customerid, c.name, c.address, sum(o.orderamount) sumorders 
FROM @Customers c INNER JOIN @Orders o ON c.customerid = o.customerid
GROUP BY c.customerid, c.name, c.address
HAVING sum(o.orderamount) > 50000
|improve this answer|||||
  • I prefer this 'classical' solution without an inline view (the inline view is the select statement in the from-clause) – miracle173 Dec 20 '19 at 2:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.