"H L" <[EMAIL PROTECTED]> wrote on 04/02/2006 11:51:48 AM: > Hi, i have a problem to select sum from same table using UNION. The key > question is there a way of combining two questions in one so the resulting > objectid in query1 is only used in query 2 in a smart way. Eg. if only one > object is avaliable in a search first year then only check that objectid for > next year and append the sum in the question. > > I have 4 tables > companyobjects that contains the key (companyid,objectid,name etc) > > and i have > calendar the table looks similar to this > (objectid,year,day1,day2.....day365) > price that looks similar to this (objectid,year,day1,day2.....day365) > reservation that looks similar to this (objectid,year,day1,day2.....day365) > > > I have tried as follows below, as you can see i want to calculate price > discount and amount to pay from same table but from 2 years. I have tried > removed all my "where" clause below and the result is not correct it is not > from both querys. > > > Maybe you know an easier way, totaly diffrent way that i have not thought of > perhaps. > > Thanks in advance /Henrik > > > SELECT > `companyobjects`.`objectid`, > Sum(`objectprice`.`d362` +`objectprice`.`d363` > +`objectprice`.`d364`+`objectprice`.`d365`), > Sum((`objectprice`.`d362` +`objectprice`.`d363` > +`objectprice`.`d364`+`objectprice`.`d365`)*0.1), > Sum((`objectprice`.`d362` +`objectprice`.`d363` > +`objectprice`.`d364`+`objectprice`.`d365`)*0.9) > FROM > `companyobjects` > Inner Join `objectprice` ON `companyobjects`.`objectid` = > `objectprice`.`objectid` > Inner Join `objectreservation` ON `companyobjects`.`objectid` = > `objectreservation`.`objectid` > Inner Join `objectcalendar` ON `companyobjects`.`objectid` = > `objectcalendar`.`objectid` > > WHERE > `companyobjects`.`companyid` = 'C050319112022656' AND > `companyobjects`.`maxnrofguests` >= '1' AND > `objectprice`.`year` = '2006' AND > `objectreservation`.`year` = '2006' AND > `objectcalendar`.`year` = '2006' > > GROUP BY > `companyobjects`.`objectid` > > UNION ALL > > SELECT > `companyobjects`.`objectid`, > Sum(`objectprice`.`d1` +`objectprice`.`d2`), > Sum((`objectprice`.`d1` +`objectprice`.`d2`)*0.1), > Sum((`objectprice`.`d1` +`objectprice`.`d2`)*0.9) > FROM > `companyobjects` > Inner Join `objectprice` ON `companyobjects`.`objectid` = > `objectprice`.`objectid` > Inner Join `objectreservation` ON `companyobjects`.`objectid` = > `objectreservation`.`objectid` > Inner Join `objectcalendar` ON `companyobjects`.`objectid` = > `objectcalendar`.`objectid` > WHERE > `companyobjects`.`companyid` = 'C050319112022656' AND > `companyobjects`.`maxnrofguests` >= '1' AND > `objectprice`.`year` = '2007' AND > `objectreservation`.`year` = '2007' AND > `objectcalendar`.`year` = '2007' > GROUP BY > `companyobjects`.`objectid` > > > > > > > I > > _________________________________________________________________ > Hitta rätt på nätet med MSN Search http://search.msn.se/
The solution is to redesign your tables. You need to split into separate columns the values you want to maintain. You do not want to keep the "flat file" design you are currently trying to use. CREATE TABLE calendar ( objectid, year, dayofyear, ... other fields... ) CREATE TABLE price ( objectid, year, dayofyear, price ) Having a separate column for each day of the year may make sense to a person but as you have discovered, it is extremely difficult to use for any kind of ad-hoc querying. A more normalized data structure will be almost as efficient in space usage but 1000s of times more efficient for querying. There is no simple way to write a query that spans years with the table structures you currently have. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
