group by izlaiž rezultātu

[metal-0-1]

Vajag palīdzību ar group by.

Ir tabula person un tabula vehicle, kas glabā info par personai piederošajiem transportlīdzekļiem.

 

person

id  |  username

1   |   bob

2   |   billy

3   |  john

 

vehicle

id | person_id | vehicle | cost  |

1      1                car         5000  

2      1                car         4000  

2      1                bike       1000

4      3                bike       500

 

Nepieciešams uzzināt, kāda ir vidējā katras personas automašīnu cena. Ja personai nepieder neviena automašīna, tad atgriež NULL. Sagaidu rezultātu:

 

name | avg_car

bob      4000

billy      NULL

john     NULL

 

Bet mans query neatgriež personu `john` vispār nemaz. http://sqlfiddle.com/#!2/5c195/2

 

Jo john pieder mocis, tātad pirmais where clause ir FALSE, bet arī otrais where clause ir FALSE, jo kaut kāds transportlīdzeklis viņam tomēr pieder.

Edited February 1, 2014 by metal-0-1

[daGrevis]

Tavs kverijs ir pareizs, bet es nedaudz pielaboju netīšām. :D

 

~~~

SELECT

`person`.`name`,

ROUND(AVG(`vehicle`.`cost`), 2) AS `cost`

FROM `person`

LEFT JOIN `vehicle` ON `person`.`id` = `vehicle`.`person_id`

WHERE `vehicle`.`vehicle` = "car"

GROUP BY `person`.`id`

~~~

 

Protams, ka tavs kverijs neatgriež Džonu. Džonam nav nevienas mašīnas, tikai velo pa 100 bakšiem.

[metal-0-1]

Protams, ka tavs kverijs neatgriež Džonu. Džonam nav nevienas mašīnas, tikai velo pa 100 bakšiem.

 

Jā, bet man Džons IR vajadzīgs => vajag, lai viņam būtu NULL. 

 

Kā lai es dabūju šo?

 

name | avg_car

bob      4000

billy      NULL

john     NULL

[jurchiks]

SELECT
`person`.`name`,
IF(
`vehicle`.`id` IS NULL
OR `vehicle`.`vehicle` != "car",
0,
ROUND(AVG(`vehicle`.`cost`), 2)
) AS `cost`
FROM `person`
LEFT JOIN `vehicle` ON `person`.`id` = `vehicle`.`person_id`
GROUP BY `person`.`id`

Result:

 

bob     2833.33
billy   0
john    0

Atgriezt NULL tur, kur ir paredzēts skaitlis, nav smuki, nemiksē kopā dažādus datu tipus. Ja vajag, atgriez -1 vai 0. Edited February 1, 2014 by jurchiks

[metal-0-1]

 Result:

bob     2833.33
billy   0
john    0

 

Not really. bobam pieder 2 mašīnas un viens velo. kvērija mērķis ir atgriezt tikai AUTOMAŠĪNU vidējo vērtību, t.i. = 4000. Tie 2833 sanāk ieskaitot arī velo.

Edited February 1, 2014 by metal-0-1

[jurchiks]

SELECT

`person`.`name`,

IFNULL(

(

SELECT ROUND(AVG(`vehicle`.`cost`), 2)

FROM `vehicle`

WHERE `person`.`id` = `vehicle`.`person_id`

AND `vehicle`.`vehicle` = "car"

),

0

) AS `avg_car`

FROM `person`

 

Edited February 1, 2014 by jurchiks

[xPtv45z]

To query iespējams uzrakstīt daudz vienkāršāku un ātrāku (sqlfiddlā uz tiem ierakstiem, 2ms pret 31ms)

SELECT 
`person`.`name`, 
round(avg(`vehicle`.`cost`)) AS `avg_car` 

FROM `person` LEFT OUTER JOIN `vehicle` 
     ON `person`.`id` = `vehicle`.`person_id` 
          AND `vehicle`.`vehicle` = 'car'
 
GROUP BY `person`.`id`

Edited February 3, 2014 by xPtv45z

[jurchiks]

Ehh, daGrevis bija tik tuvu tam pašam... Bet IFNULL tomēr vajadzētu izmantot.

Edited February 3, 2014 by jurchiks

[daGrevis]

SQL ir grūts. :D

[jurchiks]

Starp citu, tikko stackoverflowā uzgāju šādu baigi vienkāršu, labi izskaidrotu pamācību par SQL JOINiem: http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins

Pašā apakšā ir cheat sheet.

Mans query builderis šo to no tā neatbalsta, būs jāpilnveido.

Edited February 3, 2014 by jurchiks

[aaxc]

Tas ir diezgan pavecs skaidrojums jau, bet jā, noderīgs. Šeit bilde, lai nav obligāti jāapmeklē links: