|
Home > Archive > MySQL ODBC Connector > October 2005 > Sum of Sales
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| Daniel Bowett 2005-10-27, 7:47 am |
| I am unsure how to write this query, can someone help?
I have two tables.
One has a list of retailers (tbl_retailer):
retailerid
name
postcode
e.g.
1 Sprocket Supplies CH23 4PY
The other has the sales by month for each retailer:
retailerid
month_2
sales
e.g.
1 2004-01 100
1 2004-02 400
1 2004-03 300
1 2004-04 200
1 2004-05 300
What I need is a way to output a list of each retailer with two columns
at the end being sales this year to date and the equivalent sales for
the previous year.
So the columns would be:
name this_years_sale
s_to_date last_years
_sales_to_date
Total sales for ever would be a simple GROUP BY query with a sum on the
sales - but I cant see how I am going to get this info. Do I need to use
nested queries?
Regards,
Dan.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Peter Brawley 2005-10-27, 7:47 am |
| --=======AVGMAIL-435FA02F18C4=======
Content-Type: multipart/alternative; boundary=------------ 02070108070401010609
0904
-------------- 02070108070401010609
0904
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Dan,
/>...Total sales for ever would be a simple GROUP BY query with a
>sum on the sales - but I cant see how I am going to get this info.
>Do I need to use nested queries? /
You don't need nested queries. It's a crosstab or pivot table query. The
trick is to sum into one column per desired year, scope the sums on
month-to-date, and group by retailer, eg:
SELECT
r.name,
SUM(IF(LEFT(month_2,
4)='2004',sales, '')) AS '2004 Sales',
SUM(IF(LEFT(month_2,
4)='2005',sales, '')) AS '2005 Sales'
FROM tbl_retailer AS r
INNER JOIN tbl_sales AS s USING (retailerid)
WHERE SUBSTRING(month_2,6,
2)<MONTH(NOW())
GROUP BY r.name;
PB
-----
Daniel Bowett wrote:
> I am unsure how to write this query, can someone help?
>
> I have two tables.
>
> One has a list of retailers (tbl_retailer):
>
> retailerid
> name
> postcode
>
> e.g.
>
> 1 Sprocket Supplies CH23 4PY
>
> The other has the sales by month for each retailer:
>
> retailerid
> month_2
> sales
>
> e.g.
>
> 1 2004-01 100
> 1 2004-02 400
> 1 2004-03 300
> 1 2004-04 200
> 1 2004-05 300
>
>
> What I need is a way to output a list of each retailer with two
> columns at the end being sales this year to date and the equivalent
> sales for the previous year.
>
> So the columns would be:
>
> name this_years_sales_to_
date last_years_sales_to_
date
>
> Total sales for ever would be a simple GROUP BY query with a sum on
> the sales - but I cant see how I am going to get this info. Do I need
> to use nested queries?
>
> Regards,
>
> Dan.
>
>
>
-------------- 02070108070401010609
0904
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Dan,<br>
<br>
<i>>...Total sales for ever would be a simple GROUP BY query with a <br>
>sum on the sales - but I cant see how I am going to get this info. <br>
>Do I need to use nested queries?
</i><br>
<br>
You don't need nested queries. It's a crosstab or pivot table query.
The trick is to sum into one column per desired
year, scope the sums on month-to-date, and group by retailer, eg:<br>
<br>
SELECT<br>
r.name,<br>
SUM(IF(LEFT(month_2,
4)='2004',sales, '')) AS '2004 Sales',<br>
SUM(IF(LEFT(month_2,
4)='2005',sales, '')) AS '2005 Sales'<br>
FROM tbl_retailer AS r<br>
INNER JOIN tbl_sales AS s USING (retailerid)<br>
WHERE SUBSTRING(month_2,6,
2)<MONTH(NOW())<br>
GROUP BY r.name;<br>
<br>
PB<br>
<br>
-----<br>
<br>
Daniel Bowett wrote:
<blockquote cite="middjo1lr$1p8$1@sea.gmane.org" type="cite">I am
unsure how to write this query, can someone help?
<br>
<br>
I have two tables.
<br>
<br>
One has a list of retailers (tbl_retailer):
<br>
<br>
retailerid
<br>
name
<br>
postcode
<br>
<br>
e.g.
<br>
<br>
1 Sprocket Supplies CH23 4PY
<br>
<br>
The other has the sales by month for each retailer:
<br>
<br>
retailerid
<br>
month_2 <br>
sales
<br>
<br>
e.g.
<br>
<br>
1 2004-01 100
<br>
1 2004-02 400
<br>
1 2004-03 300
<br>
1 2004-04 200
<br>
1 2004-05 300
<br>
<br>
<br>
What I need is a way to output a list of each retailer with two columns
at the end being sales this year to date and the equivalent sales for
the previous year.
<br>
<br>
So the columns would be:
<br>
<br>
name this_years_sales_to_
date last_years_sales_to_
date
<br>
<br>
Total sales for ever would be a simple GROUP BY query with a sum on the
sales - but I cant see how I am going to get this info. Do I need to
use nested queries?
<br>
<br>
Regards,
<br>
<br>
Dan.
<br>
<br>
<br>
<br>
</blockquote>
</body>
</html>
-------------- 02070108070401010609
0904--
--=======AVGMAIL-435FA02F18C4=======
Content-Type: text/plain; x-avg=cert; charset=us-ascii
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Content-Description: "AVG certification"
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.361 / Virus Database: 267.12.5/149 - Release Date: 10/25/2005
--=======AVGMAIL-435FA02F18C4=======
Content-Type: text/plain; charset=us-ascii
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
--=======AVGMAIL-435FA02F18C4=======--
| |
| SGreen@unimin.com 2005-10-27, 7:47 am |
| --=_alternative 0055B6A7852570A6_=
Content-Type: text/plain; charset="US-ASCII"
news <news@sea.gmane.org> wrote on 10/26/2005 09:46:49 AM:
> I am unsure how to write this query, can someone help?
>
> I have two tables.
>
> One has a list of retailers (tbl_retailer):
>
> retailerid
> name
> postcode
>
> e.g.
>
> 1 Sprocket Supplies CH23 4PY
>
> The other has the sales by month for each retailer:
>
> retailerid
> month_2
> sales
>
> e.g.
>
> 1 2004-01 100
> 1 2004-02 400
> 1 2004-03 300
> 1 2004-04 200
> 1 2004-05 300
>
>
> What I need is a way to output a list of each retailer with two columns
> at the end being sales this year to date and the equivalent sales for
> the previous year.
>
> So the columns would be:
>
> name this_years_sales_to_
date last_years_sales_to_
date
>
> Total sales for ever would be a simple GROUP BY query with a sum on the
> sales - but I cant see how I am going to get this info. Do I need to use
> nested queries?
>
> Regards,
>
> Dan.
>
>
Try this. It's a variation on the pivot table technique. We are
column-izing on a the year value derived from the string pattern of your
month_2 column:
SELECT r.name
, sum(if(sbm.month_2 between '2004' and '2004-99',sbm.sales, 0))
as sales_2004
, sum(if(sbm.month_2 between '2005' and '2005-99',sbm.sales, 0))
as sales_2005
FROM retailers r
LEFT JOIN sales_by_month sbm
on sbm.retailerid = r.retailerid
GROUP BY r.name;
That should give you the yearly sales figures for every retailier in your
system for cy2004 and cy2005 (cy = calendar year)
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
--=_alternative 0055B6A7852570A6_=--
| |
| Daniel Bowett 2005-10-27, 7:47 am |
| Peter Brawley wrote:
> Dan,
>
> />...Total sales for ever would be a simple GROUP BY query with a
>
> You don't need nested queries. It's a crosstab or pivot table query. The
> trick is to sum into one column per desired year, scope the sums on
> month-to-date, and group by retailer, eg:
>
> SELECT
> r.name,
> SUM(IF(LEFT(month_2,
4)='2004',sales, '')) AS '2004 Sales',
> SUM(IF(LEFT(month_2,
4)='2005',sales, '')) AS '2005 Sales'
> FROM tbl_retailer AS r
> INNER JOIN tbl_sales AS s USING (retailerid)
> WHERE SUBSTRING(month_2,6,
2)<MONTH(NOW())
> GROUP BY r.name;
>
> PB
>
> -----
>
> Daniel Bowett wrote:
>
>
> ------------------------------------------------------------------------
>
> No virus found in this outgoing message.
> Checked by AVG Free Edition.
> Version: 7.1.361 / Virus Database: 267.12.5/149 - Release Date: 10/25/2005
>
>
>
> ------------------------------------------------------------------------
>
>
Thats workign great, the only problem is the WHERE clause means I only
show rows where there is sales info in the database. Sometimes there
will be no sales info in there for a particular retailer - would it be
possible to show zero for these?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Michael Stassen 2005-10-27, 7:47 am |
| Daniel Bowett wrote:
> Peter Brawley wrote:
>
>
> Thats workign great, the only problem is the WHERE clause means I only
> show rows where there is sales info in the database. Sometimes there
> will be no sales info in there for a particular retailer - would it be
> possible to show zero for these?
Yes. Change the INNER JOIN to a LEFT JOIN.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Daniel Bowett 2005-10-27, 7:47 am |
| Michael Stassen wrote:
> Daniel Bowett wrote:
>
>
>
> Yes. Change the INNER JOIN to a LEFT JOIN.
>
> Michael
>
>
I tried a LEFT JOIN earlier - it still only shows rows where there is
sales. I think it's because of the WHERE clause.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| SGreen@unimin.com 2005-10-27, 7:47 am |
| --=_alternative 007125CD852570A6_=
Content-Type: text/plain; charset="US-ASCII"
news <news@sea.gmane.org> wrote on 10/26/2005 04:02:33 PM:
> Michael Stassen wrote:
[color=darkred]
[color=darkred]
[color=darkred]
[color=darkred]
------------------------------------------------------------------------[color=darkred]
------------------------------------------------------------------------[color=darkred]
only[color=darkred]
be[color=darkred]
>
> I tried a LEFT JOIN earlier - it still only shows rows where there is
> sales. I think it's because of the WHERE clause.
>
>
Did my previous post not work? It should have given you total sales for
all retailers (regardless of if they had sales in 2005-2005)
SELECT r.name
, sum(if(sbm.month_2 between '2004' and '2004-99',sbm.sales, 0))
as sales_2004
, sum(if(sbm.month_2 between '2005' and '2005-99',sbm.sales, 0))
as sales_2005
FROM retailers r
LEFT JOIN sales_by_month sbm
on sbm.retailerid = r.retailerid
GROUP BY r.name;
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
--=_alternative 007125CD852570A6_=--
| |
| Peter Brawley 2005-10-27, 7:47 am |
| Dan,
If you don't want the query to stop with the current month, drop the
WHERE clause.
PB
Daniel Bowett wrote:
> Michael Stassen wrote:
>
>
> I tried a LEFT JOIN earlier - it still only shows rows where there is
> sales. I think it's because of the WHERE clause.
>
>
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.361 / Virus Database: 267.12.5/149 - Release Date: 10/25/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Michael Stassen 2005-10-27, 7:47 am |
| Daniel Bowett wrote:
> Michael Stassen wrote:
>
>
> I tried a LEFT JOIN earlier - it still only shows rows where there is
> sales. I think it's because of the WHERE clause.
Sorry, my answer was a bit short. You are right, the WHERE clause is the
problem. Conditions on the right side of a LEFT JOIN need to go in the ON
clause, not the WHERE clause, or it defeats the purpose of the LEFT JOIN. So,
your query would be
SELECT
r.name,
SUM(IF(LEFT(s.month_2,4)='2004',s.sales, '')) AS '2004 Sales',
SUM(IF(LEFT(s.month_2,4)='2005',s.sales, '')) AS '2005 Sales'
FROM tbl_retailer AS r
LEFT JOIN tbl_sales AS s
ON r.retailerid = s.retailerid
AND SUBSTRING(s.month_2,6,2) < MONTH(NOW())
GROUP BY r.name;
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Daniel Bowett 2005-10-27, 7:47 am |
| Michael Stassen wrote:
> Daniel Bowett wrote:
>
>
>
> Sorry, my answer was a bit short. You are right, the WHERE clause is
> the problem. Conditions on the right side of a LEFT JOIN need to go in
> the ON clause, not the WHERE clause, or it defeats the purpose of the
> LEFT JOIN. So, your query would be
>
> SELECT
> r.name,
> SUM(IF(LEFT(s.month_2,4)='2004',s.sales, '')) AS '2004 Sales',
> SUM(IF(LEFT(s.month_2,4)='2005',s.sales, '')) AS '2005 Sales'
> FROM tbl_retailer AS r
> LEFT JOIN tbl_sales AS s
> ON r.retailerid = s.retailerid
> AND SUBSTRING(s.month_2,6,2) < MONTH(NOW())
> GROUP BY r.name;
>
> Michael
>
>
Thanks for your help - that's working really well now.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
|
|
|
|
|