List:General Discussion« Previous MessageNext Message »
From:Imran Date:October 11 2005 3:19pm
Subject:Re: Help on writing a sql statement
View as plain text  
Hi Shawn:

Thank you very much for your solution. It certainly helped me in
understanding SQL a whole deal more.

I Have one followup question as to the proposed solution, This query will be
used in a multi-user situation and the logon to the database will be the
same user (a web based app) ... Since I am creating a temp table, will the
temp table creation fail for subsequent users prior to the drop i.e. the
table will exist already exist ....


best regards
Imran.

----- Original Message -----
From: <SGreen@stripped>
To: "Imran" <imran@stripped>
Cc: <mysql@stripped>
Sent: Tuesday, October 11, 2005 9:49 AM
Subject: Re: Help on writing a sql statement


> (my response bottom-posted. See below - SG)
> > ----- Original Message -----
> > From: <SGreen@stripped>
> > To: "Imran" <imran@stripped>
> > Cc: <mysql@stripped>
> > Sent: Monday, October 10, 2005 4:17 PM
> > Subject: Re: Help on writing a sql statement
> >
> >
> > > "Imran" <imran@stripped> 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
>


Thread
Help on writing a sql statementImran10 Oct
Re: Help on writing a sql statementSGreen10 Oct
  • Re: Help on writing a sql statementImran11 Oct
    • Re: Help on writing a sql statementSGreen11 Oct
      • Re: Help on writing a sql statementImran11 Oct
        • Re: Help on writing a sql statementSGreen11 Oct
          • Re: Help on writing a sql statementImran11 Oct
            • Re: Help on writing a sql statementSGreen11 Oct
      • Thank you ... Help on writing a sql statementImran29 Oct