Getting sales/delivery stats from DB.

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
facets
Forum Contributor
Posts: 273
Joined: Wed Apr 13, 2005 1:53 am
Location: Detroit

Getting sales/delivery stats from DB.

Post by facets »

Hi,

Not sure if this is the correct forum for this question but here goes..

I'd like to extract sales, deliveries and returns statistics from my database for the past 12 months and display in a graph. I'm using jpgraph and have that side organised but i'm unsure on the best way to align the data.

The following 2 lines get my deliveries and returns but obviously there are not always the same products delivered or returned so the arrays may never line up. I can't use the product table to do this as the product names are always changing.

Code: Select all

$query = "SELECT product_id, SUM(amount) AS Amount, price, date, client_id, statement_id, total FROM deliveries WHERE date BETWEEN '$selectStart' AND '$today' AND client_id = '$company_id' GROUP BY product_id";

$query = "SELECT product_id, SUM(amount) AS Amount, return_date, price, client_id, date, statement_id, total FROM returns WHERE date BETWEEN '$selectStart' AND '$today' AND client_id = '$company_id' GROUP BY product_id";
So essentially I'd like to have an array which is something like
Month ->
Product ->
Delivered (define 0 if not in delivered that month)
Returned
Sold
Can this be done purely in SQL or will I need to manipulate the data using arrays?
Any guides or pointers would be great.

tia, Will./
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

What does your orders table look like?
facets
Forum Contributor
Posts: 273
Joined: Wed Apr 13, 2005 1:53 am
Location: Detroit

Post by facets »

Here is the statements, returns and return_items tables. The deliveries table is the same setup as returns.
I have inherited this database so the layout may *not* be the best layout, please be gentle.
Statements table ->
id,
client_id,
statement_date,
due_date,
total,
notes

Returns ->
id,
client_id,
date,
refunded,
statement_id,
total

Return-items ->
id,
product_id,
amount,
return_date,
returns_id,
price
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

This may work, or at least lead you in the right direction. It's the best I could come up with based on the table structure..

Code: Select all

$query = "SELECT
            d.product_id,
            SUM(d.amount) AS Amount,
            d.price,
            date_format(d.date, '%m') as month,
            d.client_id,
            d.statement_id,
            d.total,
            (select count(*) from deliveries d2 where d2.id = s.id and d2.date BETWEEN '$selectStart' AND '$today') as delivered,
            (select count(*) from returns r where d.product_id = r.product_id and r.date BETWEEN '$selectStart' AND '$today') as returned
          FROM
            statements s
            left join deliveries d on (s.id = d.id and d.date BETWEEN '$selectStart' AND '$today')
          WHERE
            s.due_date BETWEEN '$selectStart' AND '$today'
            and s.client_id = '$company_id'
          GROUP BY
            d.product_id";
facets
Forum Contributor
Posts: 273
Joined: Wed Apr 13, 2005 1:53 am
Location: Detroit

Post by facets »

Thanks for the help so far! I have simplifies my code (with your help) to the following.
I have 3 questions now..

1. How could I workout the sales for each month. (deliveries - returns)
2. How do I merge this into one statement?
3. If there are no deliveries

Code: Select all

$query = "SELECT date_format(ret.date, '%m/%Y') as Month, 
	SUM(ret.total) AS retTotal 
	FROM returns1 ret 
	WHERE date BETWEEN '$selectStart' AND '$today' AND client_id = '$company_id' 
	GROUP BY Month
	ORDER BY Month";
	
$query1 = "SELECT date_format(del.date, '%m/%Y') as Month, 
	SUM(del.total) AS delTotal 
	FROM deliveries1 del 
	WHERE date BETWEEN '$selectStart' AND '$today' AND client_id = '$company_id' 
	GROUP BY Month
	ORDER BY Month";
Any further pointers would be great.

wil.
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post by infolock »

using a JOIN may help you out there...
facets
Forum Contributor
Posts: 273
Joined: Wed Apr 13, 2005 1:53 am
Location: Detroit

Post by facets »

One step closer :)

Code: Select all

$queryTotal = "SELECT id AS statement_id, date_format(state.statement_date, '%m') as Month, 
	SUM(state.total) AS stateTotal,
		(SELECT date_format(ret.date, '%m') as Month, SUM(ret.total) AS retTotal FROM returns1 ret) AS Returned,
		(SELECT date_format(del.date, '%m') as Month, SUM(del.total) AS delTotal FROM deliveries1 del) AS Delivered,
	FROM statements state 
	LEFT JOIN Delivered del on (state.statement_id = del.statement_id and del.date BETWEEN '2006-08-12' AND '2007-08-12') 
	LEFT JOIN Returned ret on (statestatement_id = ret.statement_id and ret.date BETWEEN '2006-08-12' AND '2007-08-12') 
	WHERE statement_date BETWEEN '2006-08-12' AND '2007-08-12' AND client_id = '107'
	GROUP BY Month 
	ORDER BY Month";
Am getting the following error..
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM statements state LEFT JOIN Delivered del on (state.statement_id = del.stat' at line 5
Any ideas?
facets
Forum Contributor
Posts: 273
Joined: Wed Apr 13, 2005 1:53 am
Location: Detroit

Post by facets »

Update.. Now using this code, but it's not finding any ret.total or del.total.
Any ideas?

Code: Select all

$queryTotal = "SELECT date_format(state.statement_date, '%m') as Month, SUM(state.total) AS stateTotal
	FROM statements state
	
	LEFT OUTER JOIN ( 
		SELECT date_format(del.date, '%m') as Month, del.statement_id, SUM(del.total) 
		AS delTotal FROM deliveries1 del 
		WHERE del.date BETWEEN '$selectStart' AND '$today' AND client_id = '$company_id' GROUP BY Month) 
	AS Delivered
	ON (state.id = Delivered.statement_id) 
	
	LEFT OUTER JOIN ( 
		SELECT date_format(ret.date, '%m') as Month, ret.statement_id, SUM(ret.total) 
		AS retTotal FROM returns1 ret 
		WHERE ret.date BETWEEN '$selectStart' AND '$today' AND client_id = '$company_id' GROUP BY Month) 
	AS Returns
	ON (state.id = Returns.statement_id)	
		
	WHERE statement_date BETWEEN '$selectStart' AND '$today' AND client_id = '$company_id'
	GROUP BY Month 
	ORDER BY Month";
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post by infolock »

it's because you aren't defining it in the "SELECT <here>" section of your query.
Post Reply