(my response bottom-posted. See below - SG)
"Imran" <[EMAIL PROTECTED]> wrote on 10/10/2005 09:16:13 PM:
> Hi Shawn .. Thanks for the offer. I am attaching the info that you
> requested.
>
> mysql> SHOW CREATE TABLE salesmaster\G;
> *************************** 1. row ***************************
> Table: salesmaster
> Create Table: CREATE TABLE `salesmaster` (
> `ID` int(10) NOT NULL auto_increment,
> `PostingDate` datetime default NULL,
> `PostingDateJulian` double(15,5) default NULL,
> `CustNo` varchar(10) default NULL,
> `ProdNo` varchar(5) default NULL,
> `SalesMan` char(2) default NULL,
> `Branch` int(10) default NULL,
> `Kilos` decimal(19,4) default '0.0000',
> `Sales` decimal(19,4) default '0.0000',
> `Cost` decimal(19,4) default '0.0000',
> PRIMARY KEY (`ID`),
> KEY `PostingDate` (`PostingDate`),
> KEY `CustNo` (`CustNo`),
> KEY `ProdNo` (`ProdNo`),
> KEY `Branch` (`Branch`)
> )
>
>
> mysql> SHOW CREATE TABLE customerintermediate\G;
> *************************** 1. row ***************************
> Table: customerintermediate
> Create Table: CREATE TABLE `customerintermediate` (
> `ID` int(10) NOT NULL auto_increment,
> `CustNo` varchar(10) default NULL,
> `CustName` varchar(120) default NULL,
> `CustShipAdd1` varchar(50) default NULL,
> `CustShipAdd2` varchar(50) default NULL,
> `CustShipAdd3` varchar(50) default NULL,
> `CustShipPostal` varchar(50) default NULL,
> `Dept` int(10) default NULL,
> `Class` int(10) default NULL,
> `Branch` int(10) default NULL,
> PRIMARY KEY (`ID`),
> KEY `CustNo` (`CustNo`),
> KEY `Branch` (`Branch`),
> KEY `Dept` (`Dept`)
> )
>
>
>
> mysql> SHOW CREATE TABLE productintermediate\G;
> *************************** 1. row ***************************
> Table: productintermediate
> Create Table: CREATE TABLE `productintermediate` (
> `ID` int(10) NOT NULL auto_increment,
> `ProdNo` varchar(5) default NULL,
> `Description` varchar(255) default NULL,
> `Status` smallint(5) default NULL,
> `Branch` int(10) default NULL,
> `Source` int(10) default NULL,
> `Main` int(10) default NULL,
> `Report` int(10) default NULL,
> PRIMARY KEY (`ID`),
> KEY `ProdNo` (`ProdNo`),
> KEY `Branch` (`Branch`)
> )
>
> ----- Original Message -----
> From: <[EMAIL PROTECTED]>
> To: "Imran" <[EMAIL PROTECTED]>
> Cc: <mysql@lists.mysql.com>
> Sent: Monday, October 10, 2005 4:17 PM
> Subject: Re: Help on writing a sql statement
>
>
> > "Imran" <[EMAIL PROTECTED]> wrote on 10/10/2005 03:52:21 PM:
> >
> > > Hi all:
> > > I need some help in writing a sql statement.
> > >
> > > I have three tables (Sales, Cust and Product). The sales table
contains
> > a
> > > large volume of data and I want to create a sql to group the sales
table
> > > then join the resultant to both the Cust and Prod and to have
additional
> > > fields selected from the Cust and Prod.
> > >
> > > So in effect something like (obviously syntax is wrong)
> > >
> > > Select sm.prodno, sm.custno, sum(cost) as costs, sum(sales) as
sales,
> > > sm.date
> > >
> > > from Sales SM where sm.date <= date(‘2005-09-01 00:00:00’) Group by
> > > sm.prodno, sm.custno, sm.date ,
> > >
> > > (Select prod.desc, prod.code, cust.custno, cust.name from cust,
Prod)
> > left
> > > join sm.prodno = prod.code left join sm.custno=cust.custno) …
> > >
> > > Any help would be greatly appreciated.
> > >
> >
> > OK, you know you need a GROUP BY, that's good. You also recognized you
> > needed to JOIN a few tables together, also good. There are at least
two
> > ways to do what you ask. One is a fairly complex query that does it
all in
> > one statement (might take a long time to compute) the other is a
sequence
> > of two simpler statements. I think the two-statement solution will be
> > easier to understand and maintain so I would prefer to go over that.
> > However, in order to provide an example of either method I will need
more
> > information from you.
> >
> > From the CLI (command line client), please provide the output from
these
> > three commands:
> >
> > SHOW CREATE TABLE sales\G;
> > SHOW CREATE TABLE cust\G;
> > SHOW CREATE TABLE product\G;
> >
> > That will tell me exactly which columns live on which tables and where
you
> > do or do not have any indexes. Good indexes will make or break the
> > performance of your database. You will not be exposing any data, only
the
> > design of the tables.
> >
> > Please remember to CC the list on all responses.
> >
> > Shawn Green
> > Database Administrator
> > Unimin Corporation - Spruce Pine
> >
Let me see if I can translate what you want in a query into regular
language. I think you would like to see, grouped by date, customer, and
product, the total cost and total sales for each (date,customer,product)
triple along with each product's description ,code, and the customer's
number and name. All of that will be limited to activity on or before
midnight of a certain date.
If I rephrased that correctly, here is how I would build your query. Step
1 is to perform the (date,customer,product) summations. By minimizing the
number of rows, columns, and/or tables we need to summarize against, we
improve performance. So I do this part of the analysis before I join in
the other tables.
Note: Date, time, and datetime literals are represented by single-quoted
strings. You do not need the DATE() function to create a date literal.
CREATE TEMPORARY TABLE tmpTotals (
key(CustNo)
, key(ProdNo)
)
SELECT PostingDate
, CustNo
, ProdNo
, sum(Cost) as costs
, sum(Sales) as sales
FROM salesmaster
WHERE PostingDate <= '2005-09-01 00:00:00'
GROUP BY PostingDate, CustNo, ProdNo;
Step 2: collect the rest of the information for the report.
SELECT CustNo
, c.Name as custname
, ProdNo
, p.Name as prodname
, costs
, sales
, PostingDate
FROM tmpTotals tt
LEFT JOIN customerintermediate c
ON c.CustNo = tt.CustNo
LEFT JOIN productintermediate p
ON p.ProdNo = tt.ProdNo
ORDER BY ... your choice... ;
Step 3: The database is not your momma. Always clean up after yourself.
DROP TEMPORARY TABLE tmpTotals;
And you are done! The only trick to doing a sequence of statements in a
row (like this) is that they all have to go through the same connection.
As long as you do not close and re-open the connection between statements,
any temp tables or @-variables you create or define remain in existence
for the life of the connection. Depending on your connection library, you
might be able to execute all three statements from a single request. Most
likely, you will need to send them in one-at-a-time.
Does this help you to organize your thoughts?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
