GoFuckYourself.com - Adult Webmaster Forum - Tech Merge duplicate table entries to calculate totals?

GoFuckYourself.com - Adult Webmaster Forum (https://gfy.com/index.php)

- Fucking Around & Business Discussion (https://gfy.com/forumdisplay.php?f=26)

- - Tech Merge duplicate table entries to calculate totals? (https://gfy.com/showthread.php?t=1352226)

Publisher Bucks

02-14-2022 02:48 AM

Merge duplicate table entries to calculate totals?

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? :helpme

Publisher Bucks

02-14-2022 03: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?).

Quote:

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

EddyTheDog

02-14-2022 04:03 AM

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

Publisher Bucks

02-14-2022 04:08 AM

Quote:

Originally Posted by EddyTheDog (Post 22964874)

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 04:15 AM

Quote:

Originally Posted by Publisher Bucks (Post 22964875)

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 12:13 PM

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 01:08 PM

Quote:

Originally Posted by ZTT (Post 22965027)

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 11: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.

All times are GMT -7. The time now is 01:46 PM.