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
This query produces the following results:
Overall, the query is working, but the jobs count only needs to count the jobs when the Balance Due column equals 0.
I have tried using
COUNT(IF(Balance_Due = 0, Salesperson_1, 0))
COUNT(CASE WHEN Balance_Due = 0 THEN Salesperson_1 ELSE 0 END)
but I always get the same results as before. The COUNT() does not change no matter what I try.
you know that every 0 counts, right?
you want this instead –
COUNT(CASE WHEN Balance_Due = 0 THEN 'ok' ELSE NULL END)
all aggregate functions like COUNT() ignore nulls
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
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
… okay hold up.
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
jmyrtle:
I had to fix the records query with your WHERE clause though. That seemed to fix the problem.
I’m gonna go ahead and bet that the other record has NULL for a Balance_Due, instead of 0.
m_hutley:
I’m gonna go ahead and bet that the other record has NULL for a Balance_Due, instead of 0.
Actually, it has a value higher than 0, so it shouldn’t be included in the individual records anyway. It’s not there anymore though.
