views:

31

answers:

1
create table Autorzy(ID int, imie varchar, nazwisko varchar);
create table Wydawnictwa(ID int, nazwa varchar, adres varchar, tel varchar);
create table Ksiazki(ID int, ISBN bigint, wydawnictwo_id int, data date, ilosc int, tytul varchar);
create table KsiazkaAutor(ID int, autor_id int, ksiazka_id int);
create table Recenzje (id int, tresc varchar, autor_id int, ksiazka_id int);

I can do:

select a.imie, a.nazwisko, count(ka.id) as IloscKsiazek
from autorzy a
left join KsiazkaAutor ka on a.id=ka.autor_id
group by a.imie, a.nazwisko;

to select count from KsiazkaAutor

or

select a.imie, a.nazwisko, count(r.id) as IloscRecenzji
from autorzy a
left join Recenzje r on a.id=r.autor_id
group by a.imie, a.nazwisko;

to select count from Recenzje .

How can I get both counts in one SELECT?

EDIT: 1st select:

"Bolesław";"Prus";3
"Adam";"Kowalski";5
"Stephen";"King";3
"Andrzej";"Sapkowski";3
"Władysław";"Reymont";3
"Richard";"Dawkins";3
"Zofia";"Nałkowska";3
"Henryk";"Sienkiewicz";3
"Terry";"Pratchett ";3
"Eliza";"Orzeszkowa";4
"Adam";"Mickiewicz";2
"John R. R.";"Tolkien";5
"Mikołaj";"Kopernik";1
"Leń";"Śmierdzący";0
"Jan";"Nowak";5
"Kamil";"Kowalik";0
"Ignacy";"Krasicki";3

2nd select:

"Bolesław";"Prus";0
"Adam";"Kowalski";0
"Stephen";"King";0
"Andrzej";"Sapkowski";0
"Władysław";"Reymont";0
"Richard";"Dawkins";0
"Zofia";"Nałkowska";0
"Henryk";"Sienkiewicz";1
"Terry";"Pratchett ";0
"Eliza";"Orzeszkowa";2
"Adam";"Mickiewicz";0
"John R. R.";"Tolkien";0
"Mikołaj";"Kopernik";0
"Leń";"Śmierdzący";0
"Jan";"Nowak";0
"Kamil";"Kowalik";0
"Ignacy";"Krasicki";0

your answer:

"Bolesław";"Prus";3;0
"Adam";"Kowalski";5;0
"Stephen";"King";3;0
"Andrzej";"Sapkowski";3;0
"Władysław";"Reymont";3;0
"Richard";"Dawkins";3;0
"Zofia";"Nałkowska";3;0
"Henryk";"Sienkiewicz";3;3
"Terry";"Pratchett ";3;0
"Eliza";"Orzeszkowa";8;8
"Adam";"Mickiewicz";2;0
"John R. R.";"Tolkien";5;0
"Mikołaj";"Kopernik";1;0
"Leń";"Śmierdzący";0;0
"Jan";"Nowak";5;0
"Kamil";"Kowalik";0;0
"Ignacy";"Krasicki";3;0

+4  A: 

Yes, you can do:

select a.imie, a.nazwisko, 
count(ka.id) as IloscKsiazek1,
count(r.id) as IloscKsiazek2
from autorzy a
left join KsiazkaAutor ka on a.id=ka.autor_id
left join Recenzje r on a.id=r.autor_id
group by a.imie, a.nazwisko;

You can also try distinct keyword in the count clause:

select a.imie, a.nazwisko, 
count(distinct ka.id) as IloscKsiazek1,
count(distinct r.id) as IloscKsiazek2
from autorzy a
left join KsiazkaAutor ka on a.id=ka.autor_id
left join Recenzje r on a.id=r.autor_id
group by a.imie, a.nazwisko;
Pablo Santa Cruz
+1 - Beat me to it!
JNK
@ksogor - based on what? postgre doesn't support multiple joins?
JNK
It doesn't work well. It returns count(r.id) equal to count(ka.id) when count(r.id)>0 and 0 otherwise.
Miko Kronn
@Miko: what do you mean by "doesn't work well"? It does not run? It does not yield expected results?
Pablo Santa Cruz
@pablo - it almost looks like from his data above (see edit to OP) that MySQL has a weird reaction to nulls in a count?
JNK
with distinct it works good :)
Miko Kronn
@Miko Kronn: Awesome. Glad it worked.
Pablo Santa Cruz