Re: Select Sum with union, tricky question perhaps not for you

2006-04-04 Thread SGreen 
H L [EMAIL PROTECTED] wrote on 04/03/2006 01:53:37 PM:

 
 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
 
 
 Hi thanks for the quick response!
 
 Well i think you are right about this. I will probably have to redesign 
the 
 database. What do you think of using DATE instead of year,dayofyear or 
is 
 there problems i could get into then.  It would be great to be able to 
use 
 select from price where date=somedate AND date = tosomedate
 
 /Henrik
 

You could use a single date column for (year, Julian date) but if you are 
constantly querying on the Julian date (based on your application or other 
needs) then having that column would be a good thing. Without a column for 
Julian date, you could not index that value either by itself or as part of 
another index.   Which way works better for you really depends on *your* 
application's needs and I cannot guess all of them. However, you really 
should normalize that data first.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: Select Sum with union, tricky question perhaps not for you

2006-04-03 Thread H L 



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



Hi thanks for the quick response!

Well i think you are right about this. I will probably have to redesign the 
database. What do you think of using DATE instead of year,dayofyear or is 
there problems i could get into then.  It would be great to be able to use 
select from price where date=somedate AND date = tosomedate


/Henrik

_
Hitta rätt på nätet med MSN Search http://search.msn.se/


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Select Sum with union, tricky question perhaps not for you

2006-04-02 Thread SGreen 
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