Question 215696 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – conditional sum sql query

My table structure is following :

CREATE TABLE `tbl_erp_invoice_lines` (
    `ID` INT(11) NOT NULL AUTO_INCREMENT,
    `INVOICE_ID` INT(11) NULL DEFAULT NULL,
    `PRODUCT_ID` INT(11) NULL DEFAULT NULL,
    `LINE_UNIT` VARCHAR(10) NULL DEFAULT 'KG' COLLATE 'utf8_bin',
    `LINE_PRICE` DECIMAL(7,3) NULL DEFAULT NULL,
    `LINE_QUANTITY` INT(11) NULL DEFAULT NULL,
    `LINE_VAT` INT(11) NULL DEFAULT '18',
    `LINE_MARK` INT(11) NULL DEFAULT NULL,
    `CREATEDON` DATETIME NULL DEFAULT current_timestamp(),
    `CREATEDBY` INT(11) NULL DEFAULT '1',
    `LASTUPDATEON` DATETIME NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
    `LASTUPDATEBY` INT(11) NULL DEFAULT '1',
)

LINE_MARK is always -1 or 1

In this table there may be several records with same PRODUCT_IDs. And those records may have a LINE_MARK -1 or 1

Now I want to SUM(LINE_PRICE*LINE_QUANTITY*LINE_MARK) for the PRODUCT_IDs which has at least one LINE_MARK=-1 in the table. If there isnt then records with this PRODUCT_ID will be excluded from SUM()

select SUM(LINE_PRICE*LINE_QUANTITY*LINE_MARK) from tbl_erp_invoice_lines 
where LINE_MARK=-1

doesn’t work because in this case every record with LINE_MARK=1 will be excluded which I don’t want

Thanks!

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    I tried to solve this question with the help of artificial intelligence. First, I consulted Bard. Despite many attempts, Bard was unable to find the correct answer. On the other hand, ChatGPT gave the correct result on its first attempt.

    And here is the correct solution

    SELECT SUM(il.LINE_PRICE * il.LINE_QUANTITY * il. LINE_MARK) as TOTAL
FROM tbl_erp_invoice_lines il
WHERE il.PRODUCT_ID IN (
    SELECT il2.PRODUCT_ID
    FROM tbl_erp_invoice_lines il2
    WHERE il2. LINE_MARK = -1
)

  2. 0

    Join with a subquery that finds all the products that have at least one LINE_MARK=-1 row.

    SELECT SUM(LINE_PRICE*LINE_QUANTITY*LINE_MARK)
FROM tbl_erp_invoice_lines AS t1
JOIN (
    SELECT DISTINCT product_id
    FROM tbl_erp_invoice_lines
    WHERE LINE_MARK = -1
) AS t2 ON t1.product_id = t2.product_id
    Login or Signup to reply.
  3. 0

    I would suggest using exists to check the existence of any valid row:

    select Sum(LINE_PRICE * LINE_QUANTITY * LINE_MARK) 
from tbl_erp_invoice_lines l
where exists (
  select 1 from tbl_erp_invoice_lines x
  where x.PRODUCT_ID = l.PRODUCT_ID and x.LINE_MARK = -1
);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search