Tech Merge duplicate table entries to calculate totals?

[Publisher Bucks]

I just started messing with our monthly sales reports and how they get collected is in CVS file by weekly sales, then a total for the month.

The monthly total CSV has the following schematic:

TITLE | BOOKID | SALES QTY | SALES $

Because the data seems like it is generated every few days, the titles are duplicated in the CSV file across the month for example:

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

How do I make it so that, if last months report has the same title mentioned 3 times, I can merge those rows AND update the total sales QTY and sales $ amounts into a single row?

In essence, it ends up looking like this:

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

Is this possible to do with an SQL query or is it simpler to do using Excel (if so how? LOL) and just reupload a 'clean' CSV to the database?

[Publisher Bucks]

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]

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

[Publisher Bucks]

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]

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]

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:

<?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]

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:

<?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]

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.