Untitled

mail@pastecode.io avatar
unknown
mysql
2 years ago
1.2 kB
2
Indexable
Never
SELECT COUNT(i.serialnumber) Serials,
    CONCAT('$',
            FORMAT(SUM(ROUND(p.wholesalecost)), 'c')) AS SumCost,
    CONCAT('$',
            FORMAT(SUM(ROUND(p.retailprice)), 'c')) AS SumPrice FROM  (SELECT
serialnumber, 
product,
defectDescription,
defectType
FROM
inventoryitem
WHERE
product LIKE '____-_Z%'
AND (location LIKE '7-A%'
OR location LIKE '7-B%'
OR location LIKE '7-C%'
OR location LIKE '7-D%'
OR location LIKE '7-U%'
OR location LIKE '7-V%')
AND sellasdefective = '1'
AND defecttype = 'non-original box'
AND qtyonhand = 1) i 
JOIN product p on p.code = i.product;

SELECT p.wholesalecost as cost, p.retailprice as price, i.* FROM (SELECT 
    serialnumber,
    product,
    size,
    location,
    sellasdefective,
    defectDescription,
    defectType
FROM
    inventoryitem
WHERE
    product LIKE '____-_Z%'
        AND (location LIKE '7-A%'
        OR location LIKE '7-B%'
        OR location LIKE '7-C%'
        OR location LIKE '7-D%'
        OR location LIKE '7-U%'
        OR location LIKE '7-V%')
        AND sellasdefective = '1'
        AND defecttype = 'non-original box'
        AND qtyonhand = 1) i
        JOIN product p on p.code = i.product