MySQL ODBC Connector - Select Sum with union, tricky question perhaps not for you

Author

Select Sum with union, tricky question perhaps not for you

H L

2006-04-02, 11:24 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/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

SGreen@unimin.com

2006-04-03, 3:27 am

--=_alternative 001747FA85257145_=
Content-Type: text/plain; charset="ISO-8859-1"
Content-Transfer-Encoding: quoted-printable

"H L" <ghl1976@hotmail.com> wrote on 04/02/2006 11:51:48 AM:

> Hi, i have a problem to select sum from same table using UNION. The=20
key=20
> question is there a way of combining two questions in one so the=20
resulting=20
> objectid in query1 is only used in query 2 in a smart way. Eg. if only=20
one=20
> object is avaliable in a search first year then only check that objectid =

for=20
> next year and append the sum in the question.
>=20
> I have 4 tables
> companyobjects that contains the key (companyid,objectid,
name etc)
>=20
> and i have
> calendar the table looks similar to this=20
> (objectid,year,day1,
day2.....day365)
> price that looks similar to this (objectid,year,day1,
day2.....day365)
> reservation that looks similar to this=20
(objectid,year,day1,
day2..... day365)
>=20
>=20
> I have tried as follows below, as you can see i want to calculate price=20
> discount and amount to pay from same table but from 2 years. I have=20
tried=20
> removed all my "where" clause below and the result is not correct it is=20
not=20
> from both querys.
>=20
>=20
> Maybe you know an easier way, totaly diffrent way that i have not=20
thought of=20

> perhaps.
>=20
> Thanks in advance /Henrik
>=20
>=20
> SELECT
> `companyobjects`.`objectid`,
> Sum(`objectprice`.`d362` +`objectprice`.`d363`=20
> +`objectprice`. `d364`+`objectprice`
.`d365`),
> Sum((`objectprice`.`d362` +`objectprice`.`d363`=20
> +`objectprice`. `d364`+`objectprice`
.`d365`)*0.1),
> Sum((`objectprice`.`d362` +`objectprice`.`d363`=20
> +`objectprice`. `d364`+`objectprice`
.`d365`)*0.9)
> FROM
> `companyobjects`
> Inner Join `objectprice` ON `companyobjects`.`objectid` =3D=20
> `objectprice`.`objectid`
> Inner Join `objectreservation` ON `companyobjects`.`objectid` =3D=20
> `objectreservation`.`objectid`
> Inner Join `objectcalendar` ON `companyobjects`.`objectid` =3D=20
> `objectcalendar`.`objectid`
>=20
> WHERE
> `companyobjects`.`companyid` =3D 'C050319112022656' AND
> `companyobjects`.`maxnrofguests` >=3D '1' AND
> `objectprice`.`year` =3D '2006' AND
> `objectreservation`.`year` =3D '2006' AND
> `objectcalendar`.`year` =3D '2006'
>=20
> GROUP BY
> `companyobjects`.`objectid`
>=20
> UNION ALL
>=20
> 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` =3D=20
> `objectprice`.`objectid`
> Inner Join `objectreservation` ON `companyobjects`.`objectid` =3D=20
> `objectreservation`.`objectid`
> Inner Join `objectcalendar` ON `companyobjects`.`objectid` =3D=20
> `objectcalendar`.`objectid`
> WHERE
> `companyobjects`.`companyid` =3D 'C050319112022656' AND
> `companyobjects`.`maxnrofguests` >=3D '1' AND
> `objectprice`.`year` =3D '2007' AND
> `objectreservation`.`year` =3D '2007' AND
> `objectcalendar`.`year` =3D '2007'
> GROUP BY
> `companyobjects`.`objectid`
>=20
>=20
>=20
>=20
>=20
>=20
> I
>=20
> =5F=5F=5F=5F=5F=5F=5
F=5F=5F=5F=5F=5F=5F=
5F=5F=5F=5F=5F=5F=5F
=5F=5F=5F=5F=
=5F=5F=5F=5F=5F=5F=5
F=5F=5F=5F=5F=5F=5F=
5F=5F=5F=5F=5F=5F=5F
=5F=5F=5F=5F=5F=
=5F=5F=5F=5F=5F=5F=5
F=5F=5F=5F=5F=5F=5F=
5F=5F=5F
> Hitta r=E4tt p=E5 n=E4tet med MSN Search http://search.msn.se/

The solution is to redesign your tables. You need to split into separate=20
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=20
person but as you have discovered, it is extremely difficult to use for=20
any kind of ad-hoc querying. A more normalized data structure will be=20
almost as efficient in space usage but 1000s of times more efficient for=20
querying. There is no simple way to write a query that spans years with=20
the table structures you currently have.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

--=_alternative 001747FA85257145_=--

H L

2006-04-03, 1:29 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

____________________
____________________
____________________
_____
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/mysql? unsub...sie.nctu.edu.tw

SGreen@unimin.com

2006-04-04, 9:27 am

--=_alternative 0049C8CC85257146_=
Content-Type: text/plain; charset="US-ASCII"

"H L" <ghl1976@hotmail.com> wrote on 04/03/2006 01:53:37 PM:

>
separate[color=darkr
ed]
"flat[color=darkred]
for[color=darkred]
with[color=darkred]
>
>
> 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

--=_alternative 0049C8CC85257146_=--

Sponsored Links