I’m making a sales report web app and I need to create a MySQL query with a conditional count method.
So far, here is my query:
SELECT Salesperson_1 as salesman,
COUNT(Salesperson_1, 0) as jobs,
SUM(IF(Balance_Due = 0, Sales_Amount, 0)) as total_sales,
SUM(IF(Balance_Due = 0, Extra_Charge_Amount, 0)) as extra_charge_total,
SUM(IF(Balance_Due = 0, Tax_Amount, 0)) as total_tax,
SUM(CASE WHEN Sales_Amount >= 1000 AND Balance_Due = 0 THEN Sales_Amount ELSE 0 END) as gtotal_above_1000,
SUM(CASE WHEN Sales_Amount < 1000 AND Balance_Due = 0 THEN Sales_Amount ELSE 0 END) as gtotal_below_1000
FROM invoices
GROUP BY Salesperson_1
which is weird now that I’m seeing this for the first time. One of these records is a full row of zeros and shouldn’t be listed.
The query that produces those records looks like this:
SELECT Salesperson_1 as salesman,
Customer_Number as customer,
LEFT(Customer_Name, 28) as name,
SUM(IF(Balance_Due = 0, Sales_Amount, 0)) as invoiced_sales,
SUM(IF(Balance_Due = 0, Extra_Charge_Amount, 0)) as extra_charge,
SUM(IF(Balance_Due = 0, Tax_Amount, 0)) as tax,
SUM(CASE WHEN Sales_Amount >= 1000 AND Balance_Due = 0 THEN Sales_Amount ELSE 0 END) as total_above_1000,
SUM(CASE WHEN Sales_Amount < 1000 AND Balance_Due = 0 THEN Sales_Amount ELSE 0 END) as total_below_1000
FROM invoices
GROUP BY Salesperson_1, Customer_Number
and the totals query looks like this:
SELECT Salesperson_1 as salesman,
COUNT(CASE WHEN Balance_Due = 0 THEN 'ok' ELSE NULL END) as jobs,
SUM(IF(Balance_Due = 0, Sales_Amount, 0)) as total_sales,
SUM(IF(Balance_Due = 0, Extra_Charge_Amount, 0)) as extra_charge_total,
SUM(IF(Balance_Due = 0, Tax_Amount, 0)) as total_tax,
SUM(CASE WHEN Sales_Amount >= 1000 AND Balance_Due = 0 THEN Sales_Amount ELSE 0 END) as gtotal_above_1000,
SUM(CASE WHEN Sales_Amount < 1000 AND Balance_Due = 0 THEN Sales_Amount ELSE 0 END) as gtotal_below_1000
FROM invoices
GROUP BY Salesperson_1
You’ve put a check in every selection for finding only when Balance_due is 0.
Instead of doing all of that…just use a WHERE clause to find records where Balance_Due is 0 to start with; and then you can simply do a count, a sum, etc?
Just seems like a lot of effort to duplicate a where?
So how can I change the other query (showing the individual records) to match the totals from the query below?
SELECT Salesperson_1 as salesman,
COUNT(CASE WHEN Balance_Due = 0 THEN 'ok' ELSE NULL END) as jobs,
SUM(Sales_Amount) as total_sales,
SUM(Extra_Charge_Amount) as extra_charge_total,
SUM(Tax_Amount) as total_tax,
SUM(CASE WHEN Sales_Amount >= 1000 THEN Sales_Amount ELSE 0 END) as gtotal_above_1000,
SUM(CASE WHEN Sales_Amount < 1000 THEN Sales_Amount ELSE 0 END) as gtotal_below_1000 FROM invoices
WHERE Balance_Due = 0
GROUP BY Salesperson_1
I’m sorry, I dont see how your records dont match?
Your screenshots show exactly 1 job, whose sales were 305.73, extra charge was 191.95, tax was 31.11, there were 0 sales over more than 1000$ (Which makes sense, given the total was 305 and change), and 305.73.
Both your screenshots show the same amounts… so… uh… ?
On that first screenshot, there was another record with all 0’s. Wouldn’t that count as 2 instead of 1?
I had to fix the records query with your WHERE clause though. That seemed to fix the problem.
SELECT Salesperson_1 as salesman,
Customer_Number as customer,
LEFT(Customer_Name, 28) as name,
SUM(Sales_Amount) as invoiced_sales,
SUM(Extra_Charge_Amount) as extra_charge,
SUM(Tax_Amount) as tax,
SUM(CASE WHEN Sales_Amount >= 1000 THEN Sales_Amount ELSE 0 END) as total_above_1000,
SUM(CASE WHEN Sales_Amount < 1000 THEN Sales_Amount ELSE 0 END) as total_below_1000
FROM invoices
WHERE Balance_Due = 0
GROUP BY Salesperson_1, Customer_Number
