---

Hej,

Har ledt efter en gruppe, der kan hjælpe med dette - har prøvet på
alt.comp.databases.sql, men endnu uden det rigtige tip. Og så falder jeg
pludselig over denne gruppe, hvor der ser ud til at sidde nogle SQL-hajer.
Så jeg prøver igen:

Jeg har 2 tabeller:

pictures:
PictureId | Title | PaintDate
001 | Flowers | 2003-11-01
002 | Sunset | 2004-07-08
003 | Fish | 2004-12-08
004 | More flowers | 2005-01-31

sales:
SalesId | PictureId | SalesDate | SalesPrice
001 | 002 | 2005-01-08 | 400
002 | 003 | 2005-01-08 | 500
003 | 004 | 2005-01-08 | 600

Jeg vil gerne lave en forespørgsel, der viser hvor mange billeder er er malt
og hvor mange der er solgt per år, dvs. nogen i stil med:

Year | Painted | Sold
2005 | 1 | 3
2004 | 2 | 0
2003 | 1 | 0

Jeg kan lave det på en tabel ad gangen:

SELECT YEAR(PaintDate) AS Year, COUNT(*) AS Painted GROUP BY YEAR(PaintDate)
ORDER BY YEAR(PaintDate) DESC;

Men i så fald skal jeg "merge" resultaterne fra de forespørgsler, og det er
jo ikke så elegant.

På alt.comp.databases.sql fik jeg forslaget:

SELECT
YEAR(pictures.PaintDate) as Year,
COUNT(pictures.PictureId) as Painted,
COUNT(sales.SalesId) as Sold
FROM pictures
LEFT JOIN sales
ON YEAR(sales.SalesDate) = YEAR(pictures.PaintDate)
GROUP BY YEAR(pictures.PaintDate)
ORDER BY YEAR(pictures.PaintDate)

men den fungerer ikke.

Hvordan så?

Mvh

Flemming

---

Flemming skrev:

> Jeg kan lave det på en tabel ad gangen:
>
> SELECT YEAR(PaintDate) AS Year, COUNT(*) AS Painted GROUP BY
> YEAR(PaintDate) ORDER BY YEAR(PaintDate) DESC;
>
> Men i så fald skal jeg "merge" resultaterne fra de
> forespørgsler, og det er jo ikke så elegant.

Hvis du med "merge" mener et join, kan jeg ikke se det uelegante:

SELECT p.theYear, p.Painted, s.Sold
FROM
(
   SELECT YEAR(PaintDate) As theYear,
       COUNT(*) AS Painted
   FROM pictures
   GROUP BY Year(PaintDate)
) p
INNER JOIN
(
   SELECT YEAR(SalesDate) As theYear,
       COUNT(*) AS Sold
   FROM sales
   GROUP BY Year(SalesDate)
) s
ON p.theYear = s.theYear
ORDER BY p.theYear

--
Jens Gyldenkærne Clausen
»Diplomatiet består netop i, at de gamle kommatister kan få lov til
at tro, at de har vundet. Men i virkeligheden har de tabt.«
Ole Togeby i Information

---

>
> Hvis du med "merge" mener et join, kan jeg ikke se det uelegante:
>

Det jeg mente med "det uelegante" var at lave 2 separate forespørgsler, og
så flette dem sammen med noget PHP-kode. Din løsning derimod ser meget
elegant ud, og mit spm. afslører jo også, at jeg bestemt ikke er en haj til
SQL. Men så læret jeg det: En forespøgrsel i forespørgslen.

> SELECT p.theYear, p.Painted, s.Sold
> FROM
> (
> SELECT YEAR(PaintDate) As theYear,
> COUNT(*) AS Painted
> FROM pictures
> GROUP BY Year(PaintDate)
> ) p
> INNER JOIN
> (
> SELECT YEAR(SalesDate) As theYear,
> COUNT(*) AS Sold
> FROM sales
> GROUP BY Year(SalesDate)
> ) s
> ON p.theYear = s.theYear
> ORDER BY p.theYear
>

Jeg prøver den fluks, og vender tilbage...

Mvh

Flemming

---

> SELECT p.theYear, p.Painted, s.Sold
> FROM
> (
> SELECT YEAR(PaintDate) As theYear,
> COUNT(*) AS Painted
> FROM pictures
> GROUP BY Year(PaintDate)
> ) p
> INNER JOIN
> (
> SELECT YEAR(SalesDate) As theYear,
> COUNT(*) AS Sold
> FROM sales
> GROUP BY Year(SalesDate)
> ) s
> ON p.theYear = s.theYear
> ORDER BY p.theYear

Så vidt jeg kan se virker ovenstående kun på år, hvor der BÅDE er malede og
solgte billeder. Hvis INNER JOIN erstattes af LEFT JOIN virker den kun på
år, hvor der er malede billeder, men ikke på år, hvor der kun er solgte
billeder.

Er der en udvej? Eller har jeg missed noget?

Mvh

Flemming

---

Flemming skrev:

> Så vidt jeg kan se virker ovenstående kun på år, hvor der BÅDE
> er malede og solgte billeder.

Det er korrekt (og uhensigtsmæssigt)

> Er der en udvej? Eller har jeg missed noget?

Du kan lave full outer join - det svarer til left + right. Kan ikke
huske om alle databaser understøtter det.

Alternativt kan du benytte følgende til at finde samtlige år, og så
lave 2 x left join til dine grupperingsforespørgsler:

SELECT * FROM
(
SELECT DISTINCT YEAR(PaintDate) as theYear
FROM pictures
UNION
SELECT DISTINCT YEAR(SalesDate) as theYear
FROM sales
) x
LEFT JOIN
( ... ) p ON x.theYear = p.theYear
LEFT JOIN
( ... ) s ON x.theYear = s.theYear

Men med tre indlejrede forespørgsler bliver den måske tung i røven.
Prøv at se om full outer join (evt. bare skrevet "outer join")
fungerer.
--
Jens Gyldenkærne Clausen
Svar venligst under det du citerer, og citer kun det der er
nødvendigt for at forstå dit svar i sammenhængen. Se hvorfor og
hvordan på http://usenet.dk/netikette/citatteknik.html

---

> > Så vidt jeg kan se virker ovenstående kun på år, hvor der BÅDE
> > er malede og solgte billeder.
>
> Det er korrekt (og uhensigtsmæssigt)
>
> > Er der en udvej? Eller har jeg missed noget?
>
> Du kan lave full outer join - det svarer til left + right. Kan ikke
> huske om alle databaser understøtter det.
>
Jeg prøver det på mit web-hoteel snarest. På Access (som jeg bruger til
små-øvelser, da det er lige ved hånden), var det ikke supporteret.

> Alternativt kan du benytte følgende til at finde samtlige år, og så
> lave 2 x left join til dine grupperingsforespørgsler:
>
> SELECT * FROM
> (
> SELECT DISTINCT YEAR(PaintDate) as theYear
> FROM pictures
> UNION
> SELECT DISTINCT YEAR(SalesDate) as theYear
> FROM sales
> ) x
> LEFT JOIN
> ( ... ) p ON x.theYear = p.theYear
> LEFT JOIN
> ( ... ) s ON x.theYear = s.theYear
>
> Men med tre indlejrede forespørgsler bliver den måske tung i røven.

Princippet er forstået - det skal forsøges. Nu er problemet med "tung i
røven" nok ikke så stort, for det er en lille database, som kører på et
web-hotel, og bruges til at hente web-sider. Der er max. et par hundrede
records i hver af de 2 tabeller. Og tilmed skal dette kun brugs så ejeren
(og hendes mand = mig) kan få lidt statistik.

Mange tak for hjælpen

Flemming

---

Flemming Jensen skrev:

> Jeg prøver det på mit web-hoteel snarest. På Access (som jeg
> bruger til små-øvelser, da det er lige ved hånden), var det
> ikke supporteret.

Hvilken base bruger du på webhotellet?


Jeg har lige afprøvet det på MSSQL 2005 - her virker det fint.

Her er en fuld forespørgsel (med andre tabel/feltnavne - det kan du
selv rette til):

SELECT ISNULL(b.theyear, p.theyear) as theYear,
   b.antal as bAntal,
   p.antal as pAntal
FROM
(
select year(oprettet) as theYear, count(*) as antal
from banner
where year(oprettet) < 2006
group by year(oprettet)
) b
FULL OUTER JOIN
(
select year(oprettet) as theYear, count(*) as antal
from partnerlink
group by year(oprettet)
) p
ON b.theYear = p.theYear


Where-delen i b er kun medtaget fordi jeg skulle tjekke hvordan det
virkede når begge tabeller havde år der ikke fandtes i den anden.
--
Bolig søges. Andel eller leje i Emdrup, Nordvest, Nørrebro, Søborg
eller Brønshøj, max 6000 pr. måned.
Kontakt pr. mail - nospam(at)gyros.dk
Jens Gyldenkærne Clausen

---

>> Jeg prøver det på mit web-hoteel snarest. På Access (som jeg
>> bruger til små-øvelser, da det er lige ved hånden), var det
>> ikke supporteret.
>
> Hvilken base bruger du på webhotellet?

Jeg bruger mySQL 5.0 (på www.one.com). Den har tilsyneladende en anden
syntax, end MSSQL, men du ved - jo mindre haj, jo mindre abstraktionsevne,
og jo mindre skridt i afprøvningen.

> SELECT ISNULL(b.theyear, p.theyear) as theYear,
> b.antal as bAntal,
> p.antal as pAntal
> FROM
> (
> select year(oprettet) as theYear, count(*) as antal
> from banner
> where year(oprettet) < 2006
> group by year(oprettet)
> ) b
> FULL OUTER JOIN
> (
> select year(oprettet) as theYear, count(*) as antal
> from partnerlink
> group by year(oprettet)
> ) p
> ON b.theYear = p.theYear
>

Som - behørigt (?) tilrettet - ikke kører - ubestemmelig syntax-fejl. Synes
at kunne se på dokumentationen, at FULL OUTER JOIN ikke er supporteret.

Derfor er jeg gået til dit forslag:

SELECT * FROM
(
SELECT DISTINCT YEAR(PaintDate) as theYear
FROM pictures
UNION
SELECT DISTINCT YEAR(SalesDate) as theYear
FROM sales
) x
LEFT JOIN
( ... ) p ON x.theYear = p.theYear
LEFT JOIN
( ... ) s ON x.theYear = s.theYear

Er nået til, at

SELECT DISTINCT YEAR(PaintDate) as theYear
FROM pictures
UNION
SELECT DISTINCT YEAR(SalesDate) as theYear
FROM sales

fungerer, og giver årstallene, men

SELECT * FROM
(
SELECT DISTINCT YEAR(PaintDate) as theYear
FROM pictures
UNION
SELECT DISTINCT YEAR(SalesDate) as theYear
FROM sales
) x

giver syntax-fejl. Arbejder videre...

Tak for hjælpen - hver gang når jeg længere og forstår mere.

Flemming

---

Flemming skrev:

> Jeg bruger mySQL 5.0 (på www.one.com).

O.k.


> SELECT * FROM
> (
> SELECT DISTINCT YEAR(PaintDate) as theYear
> FROM pictures
> UNION
> SELECT DISTINCT YEAR(SalesDate) as theYear
> FROM sales
> ) x
>
> giver syntax-fejl. Arbejder videre...

Hm - det ser ud til at fungere på min lokale mysql5-installation.

Prøv evt. at skrive aliaserne med AS:

SELECT * FROM
(....) AS x
LEFT JOIN (....) AS s
....
--
Jens Gyldenkærne Clausen
Svar venligst under det du citerer, og citer kun det der er
nødvendigt for at forstå dit svar i sammenhængen. Se hvorfor og
hvordan på http://usenet.dk/netikette/citatteknik.html

---

>> SELECT * FROM
>> (
>> SELECT DISTINCT YEAR(PaintDate) as theYear
>> FROM pictures
>> UNION
>> SELECT DISTINCT YEAR(SalesDate) as theYear
>> FROM sales
>> ) x
>>
>> giver syntax-fejl. Arbejder videre...
>
> Hm - det ser ud til at fungere på min lokale mysql5-installation.
>
> Prøv evt. at skrive aliaserne med AS:
>
> SELECT * FROM
> (....) AS x
> LEFT JOIN (....) AS s
> ...

Bare lige for at lukke denne tråd af med en tak til Jens: Efter noget
besvær, og en del skriveri med deres helpdesk, fandt jeg endelig ud af, at
one.com endnu ikke kører version 4.1 på server-siden. Men de har lovet, at
de er ved at opgradere. Så får vi at se om ikke det kører.

Flemming