CODEWITHSUNDEEP
mysql
basic_space
basic_space

Reputation: 101

Mysql compare sum of columns to columns in another table

How can I select a row from another table based on the sum of column from the left table

SELECT Group_concat(c.cartid SEPARATOR ',') AS CartIDs, 
       Sum(c.grandtotal)                    AS Sum, 
       r.percentage 
FROM   carts c 
       LEFT JOIN rebates r 
              ON Sum(c.grandtotal) >= r.fromamountpurchased 
                 AND Sum(c.grandtotal) <= r.toamountpurchased 
WHERE  c.ispaid = '1' 
       AND c.addedtorebates = '0' 
GROUP  BY c.customerid

But this doesn't work. I also tried HAVING also doesn't work.

Is it possible in one query?

Thanks!

UPDATE:

CREATE TABLE IF NOT EXISTS `carts` (
  `CartID` bigint(20) NOT NULL AUTO_INCREMENT,
  `CustomerID` bigint(20) NOT NULL,
  `GrandTotal` decimal(10,2) NOT NULL,
  `IsPaid` enum('0','1','2') NOT NULL,
  `AddedToRebates` enum('0','1') NOT NULL,
  PRIMARY KEY (`CartID`)
)

    INSERT INTO `carts` (`CartID`, `CustomerID`, `GrandTotal`,  `IsPaid`,
    `AddedToRebates`, ) VALUES
    (71, 28,  '57450.00', '1', '0' ),
    (73, 28,  '57450.00', '1', '0');



CREATE TABLE IF NOT EXISTS `rebates` (
  `RebateID` bigint(20) NOT NULL AUTO_INCREMENT,
  `Percentage` varchar(255) NOT NULL COMMENT 'in %',
  `FromAmountPurchased` decimal(10,2) NOT NULL,
  `ToAmountPurchased` decimal(10,2) NOT NULL,
  `CashEquivalent` decimal(10,2) NOT NULL,
  PRIMARY KEY (`RebateID`)
)



 INSERT INTO `rebates` (`RebateID`, `Percentage`, `FromAmountPurchased`, 
`ToAmountPurchased`, `CashEquivalent`) VALUES
    (1, '5', '50000.00', '69999.00', '3000.00'),
    (2, '10', '70000.00', '79999.00', '5000.00'),
    (3, '15', '80000.00', '89999.00', '6000.00'),
    (4, '20', '90000.00', '99999.00', '7000.00'),
    (5, '25', '100000.00', '150000.00', '8000.00'),
    (6, '0', '0.00', '49999.00', '0.00');

Upvotes: 0

Views: 1181

Answers (2)

AsafPelegCodes
AsafPelegCodes

Reputation: 1

You can achieve your result with a sub query. Please note that this sub query requires an additional scan of carts.

SELECT GROUP_CONCAT(c.CartID SEPARATOR ',') AS CartIDs, SUM(c.GrandTotal) as Sum, r.Percentage 
FROM carts c
INNER JOIN (
  SELECT SUM(GrandTotal) as grandTotal, CustomerID
  FROM carts
  GROUP BY CustomerID
) cSums ON cSums.CustomerID = c.CustomerID
LEFT JOIN rebates r ON cSums.grandTotal >= r.FromAmountPurchased AND cSums.grandTotal <= r.ToAmountPurchased
WHERE c.IsPaid = '1' AND c.AddedToRebates = '0' GROUP BY c.CustomerID

Upvotes: 0

pala_
pala_

Reputation: 9010

Try this:

select q1.CartIDs, q1.total, r.percentage
  from
    (select group_concat(c.cartid) as CartIDs, sum(c.grandtotal) as total
      from carts c
        where c.ispaid = '1'
        and c.addedtorebates = '0'
      group by c.customerid ) q1
    left join rebates r
      on q1.total >= r.fromamountpurchased 
        and q1.total <= r.toamountpurchased

Here is a demo fiddle for you: http://sqlfiddle.com/#!9/d27f5/3

You cannot use aggregate functions like SUM() in the join predicate, so in this instance, a subquery is useful

Upvotes: 0

Related Questions