[sqlite] Query Help

[Aviad Harell]

Hi,

I Have a problem with the following query executed on sqlite

SELECT   CUSTOMER,
         PRODUCT,
         [RANK]
FROM     (SELECT CUSTOMER,
                 PRODUCT,
                 [SUM_SALES],
                 (SELECT COUNT(T2.SUM_SALES)
                  FROM   (SELECT   CUSTOMER,
                                   PRODUCT,
                                   (SUM(SALES)) AS [SUM_SALES]
                          FROM     Test1MX1000Multi AS T1
                          GROUP BY CUSTOMER,
                                   PRODUCT) AS [T2]
                  WHERE  T1.CUSTOMER = T2.CUSTOMER
                         AND T2.SUM_SALES > T1.SUM_SALES) AS [RANK]
          FROM   (SELECT   CUSTOMER,
                           PRODUCT,
                           (SUM(SALES)) AS [SUM_SALES]
                  FROM     Test1MX1000Multi AS T1
                  GROUP BY CUSTOMER,
                           PRODUCT) AS [T1]) AS [TEMP]
WHERE    RANK <= 4
ORDER BY CUSTOMER,
         RANK

 i get:

misuse of aggregate: (SUM(SALES)). this query is cool under sql server

any ideas?



tnx