![]() |
![]() |
![]() |
||||
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. |
![]() ![]() |
|
Discuss what's fucking going on, and which programs are best and worst. One-time "program" announcements from "established" webmasters are allowed. |
|
Thread Tools |
![]() |
#1 |
Confirmed User
Industry Role:
Join Date: Oct 2018
Location: New Orleans, Louisiana. / Newcastle, England.
Posts: 1,129
|
![]() 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! |
![]() |
![]() ![]() ![]() ![]() ![]() |
![]() |
#2 | |
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:
__________________
SOMETHING EXTREME IS COMING SOON! |
|
![]() |
![]() ![]() ![]() ![]() ![]() |
![]() |
#3 |
Just Doing My Own Thing
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...
|
![]() |
![]() ![]() ![]() ![]() ![]() |
![]() |
#4 | |
Confirmed User
Industry Role:
Join Date: Oct 2018
Location: New Orleans, Louisiana. / Newcastle, England.
Posts: 1,129
|
Quote:
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! |
|
![]() |
![]() ![]() ![]() ![]() ![]() |
![]() |
#5 | |
Just Doing My Own Thing
Industry Role:
Join Date: Jan 2011
Location: London, Spain, New Zealand, GFY - Not Croydon...
Posts: 25,041
|
Quote:
I actually coincidently came across this today for example - https://www.youtube.com/watch?v=pBhQvLF9hsI&t=25s |
|
![]() |
![]() ![]() ![]() ![]() ![]() |
![]() |
#6 |
Confirmed User
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); ?>
__________________
__________________ |
![]() |
![]() ![]() ![]() ![]() ![]() |
![]() |
#7 | |
Confirmed User
Industry Role:
Join Date: Oct 2018
Location: New Orleans, Louisiana. / Newcastle, England.
Posts: 1,129
|
Quote:
__________________
SOMETHING EXTREME IS COMING SOON! |
|
![]() |
![]() ![]() ![]() ![]() ![]() |
![]() |
#8 |
Confirmed User
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.
__________________
__________________ |
![]() |
![]() ![]() ![]() ![]() ![]() |