Count przy join

Cześć, mam zadanie, z którym nie potrafię sobie poradzić (z bazy Northwind). Count mi zlicza zdecydowanie za dużo rekordów i nie wiem jak to rozwiąząć. Pomocy

Baza Northwind
Podaj liczbę̨ oraz wartość zamówień (bez opłaty za przesyłkę) obsłużonych przez każdego pracownika w 1997 roku. Dodatkowo dla każdego pracownika podaj informację o tym, kiedy obsłużył ostatnie zamówienie w tym roku (najpóźniejsza data zamówienia). Zbiór wynikowy powinien zawierać́ : imię̨ i nazwisko pracownika, liczbę̨ obsłużonych zamówień, datę ostatniego zamówienia, wartość obsługiwanych zamówień. Interesują̨ nas tylko pracownicy, którzy mają podwładnych i w roku 1997 obsłużyli co najmniej 40 zamówień

select e.firstname, e.lastname, count (o.orderid) as 'liczba zamówień', sum(od.unitprice * od.quantity * (1-od.discount)) as 'wartość zamówień', max(orderdate) as 'data ostatniego zamowienia'
from orders as o 
join [order details] as od 
on od.orderid = o.orderid 
join employees as e 
on o.employeeid = e.employeeid
join employees as emp
on e.employeeid = emp.reportsto
where emp.employeeid is not null
group by e.firstname, e.lastname
having count(*)>40

Zliczasz wszystkie rekordy, a powinieneś tylko poszczególen zamówienia, czyli w SQL Server: count(distinct o.orderid)

@Panczo: Dziękuję za pomoc. Już się zorientowałem w tym co napisałeś ale nadal mam problem z tą częścia: sum(distinct od.unitprice * od.quantity * (1-od.discount)) as 'wartość zamówień'
Bo wyklucza rekordy o tej samej wartości, które niekoniecznie muszą być dublami.
Może podpowiesz jak to rozwiązać?

select e.firstname, e.lastname, count (distinct o.orderid) as 'liczba zamówień', sum(dictinct od.unitprice * od.quantity * (1-od.discount)) as 'wartość zamówień', max(orderdate) as 'data ostatniego zamówienia'
from orders as o 
join [order details] as od 
on od.orderid = o.orderid 
join employees as e 
on o.employeeid = e.employeeid
join employees as emp
on e.employeeid = emp.reportsto
where emp.employeeid is not null and YEAR(o.orderdate) = 1997 
group by e.firstname, e.lastname
having count(distinct o.orderid)>40

A po co dałeś distinct do sumy?

group by e.firstname, e.lastname

Nie grupuj po imieniu i nazwisku - możesz mieć dwóch Janów Kowalskich i to nie jest ta sama osoba. Grupuj po identyfikatorze pracownika.

Co do pytania, to taki użycie distinct nie ma tam sensu. Jeżeli masz zduplikowane details (pytanie czy masz zduplikowane?), to trzeba by to rozwiązać podzapytaniem raczej (czyli SELECT w SELECT - pobrać unikalne szczegoły zamówienia i wtedy sumować).

@szarotka, @Panczo: Jak pogrupuje przez employeeid to wyskakuje mi komunikat: Column 'employees.FirstName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Jak rozumiem wszystko co zawieram w select list musi być w group by albo having (?).

Zrobiłem podzapytanie select ale wynik dalej wychodzi zły...pomocy :-)

select e.firstname, e.lastname, count (distinct o.orderid) as 'liczba zamówień', (select sum(unitprice * quantity * (1-discount)) from [Order Details]) as 'wartość zamówień', max(orderdate) as 'data ostatniego zamówienia'
from orders as o 
join [order details] as od 
on od.orderid = o.orderid 
join employees as e 
on o.employeeid = e.employeeid
join employees as emp
on e.employeeid = emp.reportsto
where emp.employeeid is not null and YEAR(o.orderdate) = 1997 
group by e.firstname, e.lastname
having count(distinct o.orderid)>=40

Nie wszystko. Tylko te kolumny, po których grupujesz (są wyjątki, ale nie będę ci zaciemniał). A Having jest dla wartości zagregowanych. I nie jest obowiązkowy

@Marcin.Miga: to z czego wynika, że jeśli chcę wyświetlić imię i nazwisko ale nie ujmę tego w group function to wyskakuje błąd?
I co skopałem w tym subselect? Dzięki za pomoc :-)

Masz błędy logiczne w tym zapytaniu, wynikające z tego, że bierzesz za dużo rekordów, jak przyjrzysz się strukturze employees to pobranie podwładnych w ten sposób:

select * from 	
	employees as e 
	join employees as emp on e.employeeid = emp.reportsto
where 
	emp.employeeid is not null

zwróci ci 2 pracowników w 8 rekordach, pomijając, że ten where przy inner joinie nie ma sensu...

Więc może skup się na wzięciu wszystkiego poprawnie i dopisaniu warunków z zadania:

select 
	e.firstname
	, e.lastname
	, count (distinct o.orderid) as 'liczba zamówień'
	, sum([wartosczamowienia] - freight) [wartość zamówień pomniejszona o koszty transportu]
	, max(o.orderdate) as 'data ostatniego zamówienia'
from 
	orders as o 
	join (select 
			orderid
			,sum((unitprice * quantity * (1-discount))) [wartosczamowienia] 
		from 
			[order details] 
		group by 
			OrderID) as od on od.orderid = o.orderid 
	join employees as e on o.employeeid = e.employeeid
where 
	--obsłużonych przez każdego pracownika w 1997 roku
	YEAR(o.orderdate) = 1997 
	--Interesują̨ nas tylko pracownicy, którzy mają podwładnych
	and o.EmployeeID in (select reportsto from Employees)
group by 
	e.firstname
	, e.lastname
having 
	-- obsłużyli co najmniej 40 zamówień
	count(distinct o.orderid)>=40

(select sum(d.unitprice * d.quantity * (1-d.discount)) from [Order Details] d JOIN orders o2 on d.orderid = o2.orderid Where o2.employeeid = e.employeeid)
pisane z palca, ale powinno zaskoczyc

Patrząc na strukturę bazy, mozna uprościć zapytanie i skorzystać z widoku Order Subtotals:

select 
	e.firstname
	, e.lastname
	, count (o.orderid) as 'liczba zamówień'
	, sum(subtotal - freight) [wartość zamówień pomniejszona o koszty transportu]
	, max(o.orderdate) as 'data ostatniego zamówienia'
from 
	orders as o 
	join [Order Subtotals] as od on od.orderid = o.orderid 
	join employees as e on o.employeeid = e.employeeid
where 
	--obsłużonych przez każdego pracownika w 1997 roku
	YEAR(o.orderdate) = 1997 
	--Interesują̨ nas tylko pracownicy, którzy mają podwładnych
	and o.EmployeeID in (select reportsto from Employees)
group by 
	e.firstname
	, e.lastname
having 
	-- obsłużyli co najmniej 40 zamówień
	count(o.orderid)>=40

Grupowanie po imieniu i nazwisku to nie jest dobry pomysł. group by e.employeeid, e.firstname, e.lastname
Możesz grupować wg kolumny, której nie masz wyszczególnionej w select

Dziękuję Wam bardzo za pomoc