--- "Datatal AB - Gauffin, Jonas" <jonas@d...> wrote:

> 1. I got a table called booking with these fields:
> bookingid
> flightnr
> price
> depart_date
>
> I want to get each flight where sum of price is larger than all
> flighs average sum of price. Please help.
> I guess that I gotto do a subselect?

I'd advice you to write a stored procedure for this one. First, find the average, and then do something like

select flightnr, sum(price)
from booking
group by flightnr
having sum(price) > :average
//I am a bit uncertain whether this is allowed

> 2. I got two tables called service and technician with these
> fields:
> service
> =====
> tech_id
> service_type
> amount_hours
>
> technician
> =======
> tech_id
> firstname
> lastname
>
> I want to show wich technician that have made most services of a
> specific type. If two technicans have made the same amount of
> services of a specific type both should be displayed.
>
> i tried something like this:
> select service_type, firstname, lastname, count(service_type)
> from service s
> inner join technician t on (s.tech_id=t.tech_id)
> group by service_type, firstname, lastname
>
> Since the group is on all 3 fields not only the best tech is shown

Create a view displaying the count for each service_type/technician, and then do

SELECT service_type, firstname, lastname, view_count
FROM service_view sv1
inner join technician t on (s.tech_id=t.tech_id)
group by service_type, firstname, lastname
where not exists(select * from service_view sv2
where sv2.service_type = sv1.service_type
and sv2.view_count > sv1.view_count)

If you fail, you can partially blame a Norwegian!
Set