Query report - sales summary without free issue

1

I have tried to get report sales summary without free issue , but the below query is not working . any suggestions

SELECT
tabSales Invoice Item.item_code,
tabSales Invoice Item.item_name,
tabSales Invoice.company,
SUM(tabSales Invoice Item.qty) AS total_quantity
FROM
tabSales Invoice
JOIN
tabSales Invoice Item ON tabSales Invoice.name = tabSales Invoice Item.parent
WHERE
tabSales Invoice.docstatus = 1
AND tabSales Invoice.posting_date BETWEEN %(from_date)s AND %(to_date)s
AND (tabSales Invoice.company = %(company)s OR %(company)s IS NULL)
AND tabSales Invoice Item.is_free_issue = 0
GROUP BY
tabSales Invoice Item.item_code,
tabSales Invoice Item.item_name,
tabSales Invoice.company
ORDER BY
tabSales Invoice.company ASC,
total_quantity DESC;

2

is it a custom field? if not, this must be is_free_item

3

Thanks for your reply. It helped
I have changed the query. Now I get the numbers but not correctly.

SELECT
tabSales Invoice Item.item_code,
tabSales Invoice Item.item_name,
tabSales Invoice.company,
SUM(CASE WHEN tabSales Invoice Item.is_free_item = 0 THEN tabSales Invoice Item.qty ELSE 0 END) AS quantity_without_free,
SUM(CASE WHEN tabSales Invoice Item.is_free_item = 1 THEN tabSales Invoice Item.qty ELSE 0 END) AS quantity_free,
SUM(tabSales Invoice Item.qty) AS total_quantity
FROM
tabSales Invoice
JOIN
tabSales Invoice Item ON tabSales Invoice.name = tabSales Invoice Item.parent
WHERE
tabSales Invoice.docstatus = 1
AND tabSales Invoice.posting_date BETWEEN %(from_date)s AND %(to_date)s
AND (tabSales Invoice.company = %(company)s OR %(company)s IS NULL)
GROUP BY
tabSales Invoice Item.item_code,
tabSales Invoice Item.item_name,
tabSales Invoice.company
ORDER BY
tabSales Invoice.company ASC,
total_quantity DESC;