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: <[EMAIL PROTECTED]> To: "Imran" <[EMAIL PROTECTED]> Cc: <mysql@lists.mysql.com> 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: <[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 > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
