LEFT JOIN og SUM()

SUM virker kun på kollapsede rækker:
 
SELECT
  (msgSUM + dataSum)
FROM
  (SELECT id , SUM( LENGTH(msg)+25+LENGTH(subject) ) as 'msgSUM' FROM msg WHERE userID=$ID GROUP BY id) M,
  (SELECT id , SUM( filesize ) as 'dataSUM'  FROM datauser WHERE userID=$ID GROUP BY data_id) D

husk at du i DATAUSER skal have en USERID...

MSG.USERID = DATAUSER.USERID

ellers ender du med noget rod :)

med mindre datauser blot er en data tabel...

Hvad med:

SELECT msg.id, LENGTH(msg.subject)+LENGTH(msg.msg)+25+SUM(data.filesize) AS datasize
FROM $DB.msg msg
LEFT JOIN $DB.msg_datauser user ON user.msg_id = msg.id
LEFT JOIN $DB.datauser data ON data.id = user.data_id
GROUP BY msg.id

Jeg har ikke lige adgang til en MySQL database, så helt sikker er jeg ikke, men det burde give størrelsen på de enkelte meddelelser (under forudsætning af, at jeg har forstået din databaseopbygning, selvfølgelig :-)). Det kan så evt. udbygges ved at arbejde videre med resultatet:

SELECT SUM(datasize) AS totaldatasize
FROM (
  SELECT msg.id, LENGTH(msg.subject)+LENGTH(msg.msg)+25+SUM(data.filesize) AS datasize
  FROM $DB.msg msg
  LEFT JOIN $DB.msg_datauser user ON user.msg_id = msg.id
  LEFT JOIN $DB.datauser data ON data.id = user.data_id
  GROUP BY msg.id
) tmp

Alternativt måske

SELECT LENGTH(msg.subject)+LENGTH(msg.msg)+25+COALESCE(sizeoffiles, 0) AS datasize
FROM $DB.msg msg
LEFT JOIN (
  SELECT user.msg_id, SUM(data.filesize) AS sizeoffiles
  FROM $DB.msg_datauser user
  INNER JOIN $DB.datauser data ON data.id = user.data_id
  GROUP BY user.msg_id
) tmp ON msg.id = tmp.msg_id

Eller hvad med:

SELECT msg.id, AVG(LENGTH(msg.subject)+LENGTH(msg.msg)+25)+SUM(data.filesize) AS datasize
FROM $DB.msg msg
LEFT JOIN $DB.msg_datauser user ON user.msg_id = msg.id
LEFT JOIN $DB.datauser data ON data.id = user.data_id
GROUP BY msg.id

Nu har du da lidt at forsøge dig med... :-)

Jeg ser lige på det senere i dag/aften :)

Men for at uddybe en smule og lige få user id med

msg (beskeder)
------
id | owner_user_id | ... | subject | msg

datauser (vedhæftede filer)
------
id | ... | filesize

msg_datauser (refererer de vedhæftede filer til den aktuelle besked)
-------
msg_id | data_id

SELECT LENGTH(msg.subject)+LENGTH(msg.msg)+25+SUM(data.filesize) AS datasize
FROM $DB.msg msg
LEFT JOIN $DB.datauser data ON data.id IN (SELECT data_id FROM $DB.msg_datauser WHERE msg_id=msg.id)
WHERE msg.owner_user_id='$UID'

Jeg skal udregne hvor meget den enkelte bruger har udnyttet af sin tilgængelige quota. Altså selve beskeden samt tilhørende/vedhæftede filer :)

her har jeg min query der tæller hver enkelt besked, og den tæller rigtigt sammen

SELECT LENGTH(msg.subject)+LENGTH(msg.msg)+25+SUM(data.filesize) AS datasize, SUM(data.filesize) AS filesize
FROM $DB.msg msg
LEFT JOIN $DB.datauser data ON data.id IN (SELECT data_id FROM $DB.msg_datauser WHERE msg_id=msg.id)
WHERE msg.id='$value'
GROUP BY msg.id

hov.. sådan her!

SELECT LENGTH(msg.subject)+LENGTH(msg.msg)+25+SUM(data.filesize) AS datasize
FROM $DB.msg msg
LEFT JOIN $DB.datauser data ON data.id IN (SELECT data_id FROM $DB.msg_datauser WHERE msg_id=msg.id)
WHERE msg.id='$value'
GROUP BY msg.id

Jeg har egentlig lidt svært ved at gennemskue de query's I har skrevet.. de virker lidt for avancerede :)

Din SQL som vist svarer ganske nøje til den i min første kommentar kl. 17/09-2007 21:29:26, så vidt jeg kan se. Prøv at køre den på en enkelt id sådan som du gør i din egen forespørgsel og se om ikke resultatet bliver det samme.

Forskellen er, at jeg har benyttet 2 joins, hvor du har benyttet en en join, hvor du som joinkriterie benytter en subselect. Det virker temmelig geeket på mig. Virkelig mærkelig måde at gøre det på.

Nå, uanset hvordan denne forespørgsel bliver sammensat, så vil GROUP BY på msg.id betyde, at du får en række for hver meddelelse. Og det er faktisk den måde du er nødt til at gøre det. Hvis du i stedet grupperede på f.eks. brugerid, brugerid el. lign. ville du jo så også være nødt til at sætte en SUM på de andre felter (dem fra msg aliaset). Men da der muligvis er tilknyttet flere vedhæftede filer, ville det medføre at disse felter blev talt med lige så mange gange som der er vedhæftede filer). Alternativt, hvis du ikke satte sum på felterne (hvilket MySQL, som den mig bekendt eneste database, af uransagelige grunde tillader - normalt ville alle felter, som ikke indgår i en GROUP BY skulle sættes ind i en aggregat funktion), ville MySQL vælge værdier fra en tilfældig række med samme bruger, hvilket ikke er særligt betryggende. Du vil i hvert fald ikke kunne være sikker på at få det korrekte resultat.

Du er derfor nødt til at arbejde videre med den sum du finder frem til pr. meddelelse. Det gøres ved at bruge den første forespørgsel som base for den næste hvor du så kan gruppere på brugeren:

SELECT bruger, sum(datasize) as datasize
FROM (
  dinForespørgselMedBeregningPrMeddelelse
) AS tmp
GROUP BY bruger

Du laver altså en forespørgsel, som bruger den oprindelige opsummering pr. meddelelse som "brændstof".

I din "indre" forespørgsel er du så selvfølgelig nødt til at medtage det bruger felt, som den "ydre" forespørgsel skal bruge til at gruppere på.

Jeg håber du er blevet lidt mere klar over, hvad der sker i mine "mærkelige" forespørgsler. :-)

det er altså svar "17/09-2007 22:47:09" du hentyder til? :)

i så fald returnerer den kun samlet datasize for sidste besked

Nej, det var nu den jeg skrev, jeg mener ligger tættest på den SQL du viste her til sidst. Men hvis vi nu for et øjeblik glemmer min "optimering" af din SQL (som burde være noget hurtigere end en med en subselect), så prøv at sætte din egen version ind i en "ydre" opsummering, sådan som jeg skrev i den seneste meddelelse.

SELECT tmp.owner_user_id, SUM(tmp.datasize) AS usedspace
FROM (
SELECT msg.owner_user_id, LENGTH(msg.subject)+LENGTH(msg.msg)+25+SUM(data.filesize) AS datasize
FROM $DB.msg msg
LEFT JOIN $DB.datauser data ON data.id IN (SELECT data_id FROM $DB.msg_datauser WHERE msg_id=msg.id)
GROUP BY msg.id
) as tmp
GROUP BY tmp.owner_user_id

Jeg synes stadig den driller lidt, men tror jeg har fundet fejlen

SELECT msg.id, LENGTH(msg.subject)+LENGTH(msg.msg)+25+SUM(data.filesize) AS datasize
FROM ((msg msg
    LEFT JOIN msg_datauser msg_data ON msg_data.msg_id=msg.id)
    LEFT JOIN datauser data ON data.id=msg_data.data_id)
GROUP BY msg.id

Hvis der ikke findes vedhæftede filer i beskeden returnerer den NULL

Hvis jeg går tilbage til den oprindelige problemstilling passer det også nøjagtigt med at de beskeder som ikke har vedhæftede filer ikke tælles med. Jeg kan umiddelbart bare ikke se hvorfor den ikke gør det?

Du kan undgå null ved at bruge COALESCE funktionen i forbindelse med SUM:

SUM(COALESCE(data.filesize, 0)) AS datasize

eller

COALESCE(SUM(data.filesize), 0) AS datasize

Den vil ændre null til tallet 0.

jamen det er jo en fejl at den returnerer NULL :)

hvis der ikke findes vedhæftede filer til beskeden skulle den da gerne udskrive størrelsen for den enkelte række i 'msg' tabellen

Jeg kan bare ikke se hvad der gør at den ikke altid returnerer en størrelse??

undskyld.. jeg er for hurtig.. det virker med COALESCE

Ja, problemet er, at når man bruger LEFT JOIN er der jo mulighed for, at der ikke er nogen rækker i tabellen på LEFT JOIN som tilfredsstiller ON kriteriet. Så vil der blive returneret Null (også SUM funktionen vil i så fald returnere Null.

Null + en anden værdi vil altid være Null. Her er COALESCE funktionen handy, da den konverterer Null til en anden værdi, i dette tilfælde 0. Dermed er det en helt alm. addition, som ikke giver problemer.

jeg har i hvert fald lært noget nyt gennem det her spm.. tak for det :)

husk nu at lave et svar

Det gør jeg så... :-)

Har du fået opsummeret pr. bruger, sådan som du ville?

jep.. det dur bare :)

        $sql = "SELECT tmp.owner_user_id, SUM(tmp.datasize) AS datasize
            FROM (
                SELECT msg.owner_user_id, LENGTH(msg.subject)+LENGTH(msg.msg)+25+SUM(COALESCE(data.filesize, 0)) AS datasize
                FROM ($DB.msg msg
                    LEFT JOIN $DB.msg_datauser msg_data ON msg_data.msg_id=msg.id)
                LEFT JOIN $DB.datauser data ON data.id=msg_data.data_id
                GROUP BY msg.id
            ) AS tmp
            GROUP BY tmp.owner_user_id";