Merge duplicate table entries to calculate totals?

**Publisher Bucks** · 02-14-2022, 01:34 AM

So I managed to bastardize some code I found to merge the data but, I can't figure out where I should put the ORDER BY 'Quantity' ASC part (is this even the right query?).

$result = mysqli_query($mysqli, "SELECT `Title`, SUM(`Quantity`) AS `Quantity` FROM `Table` GROUP BY `Title`") ;

**EddyTheDog** · 02-14-2022, 02:03 AM

For reports try Google Data Studio - It's built for exactly what you are trying to do...

**Publisher Bucks** · 02-14-2022, 02:08 AM

Originally posted by EddyTheDog

For reports try Google Data Studio - It's built for exactly what you are trying to do...

I need this to be centralized so staff can login and see parts of it without having access to the full data sheet depending on what that staff member actual does work wise.

They all have their own admin dashboard which contains information pertinent to their role, even though it pulls all the data from a central database.

**EddyTheDog** · 02-14-2022, 02:15 AM

Originally posted by Publisher Bucks

I need this to be centralized so staff can login and see parts of it without having access to the full data sheet depending on what that staff member actual does work wise.

They all have their own admin dashboard which contains information pertinent to their role, even though it pulls all the data from a central database.

That can be done - It would be reporting only though - It would not be an 'action' dashboard...

I actually coincidently came across this today for example - https://www.youtube.com/watch?v=pBhQvLF9hsI&t=25s

**ZTT** · 02-14-2022, 10:13 AM

I don't know what you're trying to accomplish overall, but to group and add the figures together you could...

Process the original CSV to look like this:

Book1,001,12,340
Book2,002,10,260
Book1,001,8,240
Book5,005,2,60

And from that you'd produce a CSV like this:

Book1,001,20,580
Book2,002,10,260
Book5,005,2,60

From this:

Code:

<?php

$data = array_map('str_getcsv', file('data.csv'));

foreach($data as $datas){
    $x = $datas[0];
    if(isset($book[$x])){
        $book[$x][2] = $book[$x][2] + $datas[2];
        $book[$x][3] = $book[$x][3] + $datas[3];
    }else{
        $book[$x] = $datas;
    }
}

$sum = fopen('sum.csv', 'w');

foreach ($book as $books) {
    fputcsv($sum, $books);
}

fclose($sum);
?>

**Publisher Bucks** · 02-14-2022, 11:08 AM

Originally posted by ZTT

I don't know what you're trying to accomplish overall, but to group and add the figures together you could...

Process the original CSV to look like this:

Book1,001,12,340
Book2,002,10,260
Book1,001,8,240
Book5,005,2,60

And from that you'd produce a CSV like this:

Book1,001,20,580
Book2,002,10,260
Book5,005,2,60

From this:

Code:

<?php

$data = array_map('str_getcsv', file('data.csv'));

foreach($data as $datas){
    $x = $datas[0];
    if(isset($book[$x])){
        $book[$x][2] = $book[$x][2] + $datas[2];
        $book[$x][3] = $book[$x][3] + $datas[3];
    }else{
        $book[$x] = $datas;
    }
}

$sum = fopen('sum.csv', 'w');

foreach ($book as $books) {
    fputcsv($sum, $books);
}

fclose($sum);
?>

Thanks, I actually have the data consoiidated how I need it, I'm just having an issue displaying it by SALES $ order, when I put ORDER BY sales in the SQL query its not working (based on the query I posted above).

**ZTT** · 02-14-2022, 09:10 PM

I try to avoid using databases, or even PHP, as much as possible, but if I have to, I use SQLite. From table 'data', column names book,id,units,amount this does what you seem to want:

select book,id,sum(units),sum(amount) from data group by book order by sum(amount) ASC;

MySQL is probably similar.

Merge duplicate table entries to calculate totals?

Tech Merge duplicate table entries to calculate totals?

Comment

Comment

Comment

Comment

Comment

Comment

Comment