Welcome to the GoFuckYourself.com - Adult Webmaster Forum forums.

You are currently viewing our boards as a guest which gives you limited access to view most discussions and access our other features. By joining our free community you will have access to post topics, communicate privately with other members (PM), respond to polls, upload content and access many other special features. Registration is fast, simple and absolutely free so please, join our community today!

If you have any problems with the registration process or your account login, please contact us.

Post New Thread Reply

Register GFY Rules Calendar
Go Back   GoFuckYourself.com - Adult Webmaster Forum > >
Discuss what's fucking going on, and which programs are best and worst. One-time "program" announcements from "established" webmasters are allowed.

 
Thread Tools
Old 02-14-2022, 02:48 AM   #1
Publisher Bucks
Confirmed User
 
Industry Role:
Join Date: Oct 2018
Location: New Orleans, Louisiana. / Newcastle, England.
Posts: 1,129
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?
__________________
SOMETHING EXTREME IS COMING SOON!
Publisher Bucks is offline   Share thread on Digg Share thread on Twitter Share thread on Reddit Share thread on Facebook Reply With Quote
Old 02-14-2022, 03:34 AM   #2
Publisher Bucks
Confirmed User
 
Industry Role:
Join Date: Oct 2018
Location: New Orleans, Louisiana. / Newcastle, England.
Posts: 1,129
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`") ;
__________________
SOMETHING EXTREME IS COMING SOON!
Publisher Bucks is offline   Share thread on Digg Share thread on Twitter Share thread on Reddit Share thread on Facebook Reply With Quote
Old 02-14-2022, 04:03 AM   #3
EddyTheDog
Just Doing My Own Thing
 
EddyTheDog's Avatar
 
Industry Role:
Join Date: Jan 2011
Location: London, Spain, New Zealand, GFY - Not Croydon...
Posts: 25,041
For reports try Google Data Studio - It's built for exactly what you are trying to do...
EddyTheDog is online now   Share thread on Digg Share thread on Twitter Share thread on Reddit Share thread on Facebook Reply With Quote
Old 02-14-2022, 04:08 AM   #4
Publisher Bucks
Confirmed User
 
Industry Role:
Join Date: Oct 2018
Location: New Orleans, Louisiana. / Newcastle, England.
Posts: 1,129
Quote:
Originally Posted by EddyTheDog View Post
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.
__________________
SOMETHING EXTREME IS COMING SOON!
Publisher Bucks is offline   Share thread on Digg Share thread on Twitter Share thread on Reddit Share thread on Facebook Reply With Quote
Old 02-14-2022, 04:15 AM   #5
EddyTheDog
Just Doing My Own Thing
 
EddyTheDog's Avatar
 
Industry Role:
Join Date: Jan 2011
Location: London, Spain, New Zealand, GFY - Not Croydon...
Posts: 25,041
Quote:
Originally Posted by Publisher Bucks View Post
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
EddyTheDog is online now   Share thread on Digg Share thread on Twitter Share thread on Reddit Share thread on Facebook Reply With Quote
Old 02-14-2022, 12:13 PM   #6
ZTT
Confirmed User
 
ZTT's Avatar
 
Industry Role:
Join Date: Apr 2019
Posts: 657
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);
?>
__________________
__________________
ZTT is offline   Share thread on Digg Share thread on Twitter Share thread on Reddit Share thread on Facebook Reply With Quote
Old 02-14-2022, 01:08 PM   #7
Publisher Bucks
Confirmed User
 
Industry Role:
Join Date: Oct 2018
Location: New Orleans, Louisiana. / Newcastle, England.
Posts: 1,129
Quote:
Originally Posted by ZTT View Post
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).
__________________
SOMETHING EXTREME IS COMING SOON!
Publisher Bucks is offline   Share thread on Digg Share thread on Twitter Share thread on Reddit Share thread on Facebook Reply With Quote
Old 02-14-2022, 11:10 PM   #8
ZTT
Confirmed User
 
ZTT's Avatar
 
Industry Role:
Join Date: Apr 2019
Posts: 657
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.
__________________
__________________
ZTT is offline   Share thread on Digg Share thread on Twitter Share thread on Reddit Share thread on Facebook Reply With Quote
Post New Thread Reply
Go Back   GoFuckYourself.com - Adult Webmaster Forum > >

Bookmarks

Tags
sales, total, book1, csv, month, merge, qty, title, file, book5, $60, monthly, $260, book2, times, rows, months, report, mentioned, simpler, query, excel, lol, helpme, database



Advertising inquiries - marketing at gfy dot com

Contact Admin - Advertise - GFY Rules - Top

©2000-, AI Media Network Inc



Powered by vBulletin
Copyright © 2000- Jelsoft Enterprises Limited.