CODEWITHSUNDEEP
mysql
Ben Simmons
Ben Simmons

Reputation: 329

MySQL Use of Having and Where Clause

Having difficulty returning the desired results.

Here is my query: 

SELECT
    DATABASE()AS INVOICES_Range_$0,
    count(
        DISTINCT invoice_lines.invoice_header_id
    )AS 'Invoice Header Count',
    sum(invoice_lines.accounting_total)AS 'Dollar_Value',
    100 * COUNT(
        DISTINCT invoice_lines.invoice_header_id
    )/(
        SELECT
            COUNT(
                DISTINCT invoice_lines.invoice_header_id
            )
        FROM
            invoice_lines
        WHERE
            (
                invoice_lines. STATUS NOT LIKE '%new%'
            )
        AND(
            invoice_lines. STATUS NOT LIKE '%voided%'
        )
    )AS 'Percent of All Invoices',
    COUNT(approvals.approvable_id)/ count(
        DISTINCT invoice_lines.invoice_header_id
    )AS 'AVG_APPROVALS'
FROM
    invoice_lines
LEFT JOIN approvals ON invoice_lines.invoice_header_id = approvals.approvable_id
WHERE
    (
        invoice_lines.accounting_total = 0
    )
AND(
    invoice_lines. STATUS NOT LIKE '%new%'
)
AND(
    invoice_lines. STATUS NOT LIKE '%voided%'
);

This query returns results where any invoice line has a value of $0.

For reference, we may have an invoice where one line is $0 but the other lines total $600.

I am wanting to only include in the above query where the total of all the invoice lines equal $0.

I've tried:

    SELECT
    DATABASE()AS INVOICES_Range_$0,
    count(
        DISTINCT invoice_lines.invoice_header_id
    )AS 'Invoice Header Count',
    sum(invoice_lines.accounting_total)AS 'Dollar_Value',
    100 * COUNT(
        DISTINCT invoice_lines.invoice_header_id
    )/(
        SELECT
            COUNT(
                DISTINCT invoice_lines.invoice_header_id
            )
        FROM
            invoice_lines
        WHERE
            (
                invoice_lines. STATUS NOT LIKE '%new%'
            )
        AND(
            invoice_lines. STATUS NOT LIKE '%voided%'
        )
    )AS 'Percent of All Invoices',
    COUNT(approvals.approvable_id)/ count(
        DISTINCT invoice_lines.invoice_header_id
    )AS 'AVG_APPROVALS'
FROM
    invoice_lines
LEFT JOIN approvals ON invoice_lines.invoice_header_id = approvals.approvable_id
WHERE
    (
        invoice_lines. STATUS NOT LIKE '%new%'
    )
AND(
    invoice_lines. STATUS NOT LIKE '%voided%'
)
HAVING
    SUM(
        invoice_lines.accounting_total = 0
    );

However, that returns the same results. Also, when modified to 

HAVING (SUM(invoice_lines.accounting_total) < 500 )

It returns all invoices and the total amount.

Any assistance would be greatly appreciated, as I cannot determine the proper method for limiting my results to those invoice_header_id to only count those invoices where the sum of all lines is equal to 0.

Upvotes: 0

Views: 64

Answers (2)

Ben Simmons
Ben Simmons

Reputation: 329

The solution was to evaluate in the WHERE clause using the 'Dollar Value' identified earlier in the query.

I changed the SUM(invoice_lines.accounting_total) as TOTAL

and then in the WHERE clause I added AND (TOTAL = 0);

Worked like a champ.

Upvotes: 0

LoztInSpace
LoztInSpace

Reputation: 5697

HAVING
    SUM(
        invoice_lines.accounting_total = 0
    );

probably wants to be 

HAVING
    SUM(
        invoice_lines.accounting_total
    )  = 0

Upvotes: 1

Related Questions