what happened when you tested it? ™
The totals are made, but they appear twice?
SELECT Customer_Number as customer,
LEFT(Customer_Name, 28) as name,
SUM(Sales_Amount) as invoiced_sales,
SUM(CASE WHEN Sales_Amount >= 1000 THEN Sales_Amount ELSE 0 END) as total_above_1000,
SUM(CASE WHEN Sales_Amount >= 1000 THEN ROUND(Sales_Amount * 0.09, 2) ELSE 0.00 END) as comm_above_1000,
SUM(CASE WHEN Sales_Amount < 1000 THEN Sales_Amount ELSE 0 END) as total_below_1000,
SUM(CASE WHEN Sales_Amount < 1000 THEN ROUND(Sales_Amount * 0.045, 2) ELSE 0.00 END) as comm_below_1000,
SUM(CASE WHEN Sales_Amount >= 1000 THEN ROUND(Sales_Amount * 0.09, 2) ELSE 0.00 END + CASE WHEN Sales_Amount < 1000 THEN ROUND(Sales_Amount * 0.045, 2) ELSE 0.00 END) as total_comm
FROM invoices
WHERE Balance_Due = 0 AND Salesperson_1 = 'BK' AND Sales_Amount != 0
GROUP BY Salesperson_1, Customer_Number WITH ROLLUP
try running your query for a salesperson that has more than one customer
I am. There are multiple customers in this table since there are multiple customer numbers. In this case, the salesperson sold jobs to 6 customers. The last two are NULL due to the WITH ROLLUP clause.
ah, okay, i see where i went wrong
you have customer totals, one row per customer
then you have the salesperson’s total – because you’re grouping by salesperson
then you have the grand total, which happens to be for only one salesperson
if you remove the salesperson from the GROUP BY, there will only be customer totals and grand totals
and you can do this because you’re running the query for only one salesperson
That worked!
I’m noticing that the name column is duplicated twice at the very end. How do I remove the duplicate value and make that NULL to where nothing is displayed there?
That was for a different query for another report that has already been made.
If I can’t use WITH ROLLUP how can I do it in PHP?
The same way you do it in any other language. You add the values up yourself, or you seek for the value you know is going to be null, and handle the exception case.
Honestly it’s been long enough since I’ve seen your PHP that I could only hazard to guess at this point but if (empty($row['cust_no'])) { //do whatever you're going to do. }
Here is the section of the report I’m working on:
function viewTotals() {
$this->SetFont('Arial', '', 10);
global $pdo;
$sql = "
SELECT Customer_Number as customer,
LEFT(Customer_Name, 28) as name,
SUM(Sales_Amount) as invoiced_sales,
SUM(CASE WHEN Sales_Amount >= 1000 THEN Sales_Amount ELSE 0 END) as total_above_1000,
SUM(CASE WHEN Sales_Amount >= 1000 THEN ROUND(Sales_Amount * 0.09, 2) ELSE 0.00 END) as comm_above_1000,
SUM(CASE WHEN Sales_Amount < 1000 THEN Sales_Amount ELSE 0 END) as total_below_1000,
SUM(CASE WHEN Sales_Amount < 1000 THEN ROUND(Sales_Amount * 0.045, 2) ELSE 0.00 END) as comm_below_1000,
SUM(CASE WHEN Sales_Amount >= 1000 THEN ROUND(Sales_Amount * 0.09, 2) ELSE 0.00 END + CASE WHEN Sales_Amount < 1000 THEN ROUND(Sales_Amount * 0.045, 2) ELSE 0.00 END) as total_comm
FROM invoices
WHERE Balance_Due = 0 AND Salesperson_1 = 'BK' AND Sales_Amount != 0
GROUP BY Customer_Number";
$stmt = $pdo->prepare($sql);
$stmt->execute();
while($data = $stmt->fetch(PDO::FETCH_OBJ)) {
$x = $this->GetX();
$this->SetX(70);
$this->Cell(20, 10, $data->customer, 1, 0, 'C');
$this->Cell(70, 10, $data->name, 1, 0, 'C');
$this->Cell(25, 10, $data->invoiced_sales, 1, 0, 'C');
$this->Cell(20, 10, $data->total_comm, 1, 0, 'C');
$this->Ln();
$this->SetX($x);
}
$this->Ln();
$this->SetFont('Arial', 'I', 8);
$this->Cell(265, 5, '* Jobs are grouped by salesperson and customer number', 0, 0, 'C');
}
}
I’m now confused, as you seem to have gone from this advice:
to “I’ll remove the salesperson AND the rollup”.
If all you want to do is take the rollup row and replace the customer number (which is blank in the rollup row) with something else, you leave the rollup in and do the conditional check on the customer number.
If you want to take the rollup row out entirely and do it yourself at the end, then you’ll have to instantiate a series of counting variables to 0 at the start, then inside the while loop add the relevant numbers to your counting variables, and then after the while loop output another row to your report containing your results.
Personally i’d stick with the rollup row.
This is all I need to do, yes. However, the name value is duplicated. I need to remove the duplicated value.
Same check though - the check tells you you’re in the rollup row. You can then choose not to output the name.
So
LEFT(Customer_Name, 28) as name
needs to be
CASE WHEN Customer_Name IS NOT NULL THEN LEFT(Customer_Name, 28) ELSE ' ' as name
?
no… we’re talking about your PHP now, not the query. You’re done with the query. It gives you what you want; you just want to change the output displayed from it.
So I know I need an IF statement somewhere. But doesn’t this:
$this->Cell(20, 10, $data->customer, 1, 0, 'C');
$this->Cell(70, 10, $data->name, 1, 0, 'C');
$this->Cell(25, 10, $data->invoiced_sales, 1, 0, 'C');
$this->Cell(20, 10, $data->total_comm, 1, 0, 'C');
have to be an array?
Well it’s not an array now, so if it has to be an array, it’s already wrong.
I think what you’re trying to say is ‘dont I have to output all of these things for every row’, to wit, yes, but you choose what goes into the data.
I’ll be honest, I have no idea what library you’re using, because $this->Cell tells me nothing about what the values mean what’s 20,10 mean? 1,0,‘C’? Don’t know. But I can tell you that $data->name is outputting whatever was in the row’s ‘name’ field, and is evidently the bit you’re trying to monkey with.
I am using FPDF to create the report.
$this->Cell(20, 10, $data->name, 1, 0, 'C');
$this->Cell() determines there is a new cell being made in the table
20 is the cell width
10 is the cell height
$data->name is the value inside the cell
1 defines the table borders (if set to 0, no borders are visible)
0 defines the current position for the next record (if 1, a new line is created before the new record is inserted)
'C' defines text alignment within the cell
Isn’t that what the while loop is for?
So, I have to use an if() statement to check for a duplicate value and if it’s there, make it blank, otherwise list it?
if (name != duplicate) {
// hide or remove the value
} else {
// list the value
Close, check to see if the customer number is empty; if it is, create a cell with an empty string as the data point. If it isnt, create a cell with the row’s name value.