3 best buyers per year

[antionio]

Hello, can anybody help me with this? I have a 2500 records in one table where i have registered the last 5 years sales. How can I
>>sum all the sales of a customer in a given year
>>get the top 3 buyers in a given year
>>for the whole period (5 years)
>>in only one sql stament, if possible.

Thanks a lot from Spain

 

[PHV]

What have you tried so far and where in your SQL code are you stuck ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[antionio]

Thanks PHV, my last attempt was....

SELECT A.cliente, A.total, A.año
FROM PEDIDOS
INNER JOIN (
SELECT cliente, sum(parcial) as total, year(fecha) as año
FROM PEDIDOS
GROUP BY año
ORDER BY año, total
LIMIT 3) AS A

what gives lots of duplicated records.

 

[r937]

WITH client_year_totals 
     ( cliente
     , total
     , año )
AS (
SELECT cliente
     , SUM(parcial) 
     , YEAR(fecha) 
  FROM PEDIDOS 
GROUP 
    BY cliente
     , YEAR(fecha) 
   )
select cliente
     , total
     , año
  from client_year_totals as CTE
 where 3 >
       ( select count(*)
           from client_year_totals
          where año = CTE.año
            and total > CTE.total )

this might not work for you, because it looks like you're using mysql and mysql doesn't support common table expressions

but then, you did post in the ANSI SQL forum

[small]note that YEAR() should probably be replaced by EXTRACT()[/small]

what you would need to do is declare a view for client_year_totals, or save it as a temporary table

r937.com | rudy.ca

 

[antionio]

I r937, it works perfectly!! I followed your suggestions and first made a table 'client_year_totals' and cut part of your code like this:

select cliente
, total
, año
from TOTALES as CTE
where 3 >
( select count(*)
from TOTALES
where año = CTE.año
and total > CTE.total )
order by año desc, total desc