Link to home
Start Free TrialLog in
Avatar of trbbhm
trbbhmFlag for Afghanistan

asked on

Using MAX function with GROUP By Function

Experts:

I have a SQL query that shows the number of times a customer shops at a particular store.  I need a way to modify this query so that it returns only the one store where the customer shops the most.

Below is my query:

SELECT CUSTNUM, STORENUM, COUNT(STORENUM) AS FAV FROM CUSTSLS,CUSTOMER
WHERE CUSTOMER.CUSTOMERNUM=CUSTSLS.CUSTNUM
AND NOT (CUSTNUM IN (1,2,3,4)
AND CUSTSLS.FORMAT='D'
AND MASTERCUSTNUM=0
AND ACCOUNTTYPE IN ('A','B','C','D')
AND (DEPT<70 OR DEPT IN (76,78))
GROUP BY CUSTNUM,STORENUM
ORDER BY CUSTNUM,FAV DESC


This essentially returns data similar to the following:

10     1     5
10     2     3
10     4     2
20     4     5
20     1     2
30     4     4
30     2     1
40     1     8
40     4     5
40     2     1


Now I would really like to boil this down to the following results:

10     1     5
20     4     5
30     4     4
40     1     8

Any suggestions on how I can modify my query to do this?  Thanks!!!
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

try this


Select T.CustNum, T.Storenum,max(T.Fav)
FROM
(SELECT CUSTNUM, STORENUM, COUNT(STORENUM) AS FAV FROM CUSTSLS,CUSTOMER
WHERE CUSTOMER.CUSTOMERNUM=CUSTSLS.CUSTNUM
AND NOT (CUSTNUM IN (1,2,3,4)
AND CUSTSLS.FORMAT='D'
AND MASTERCUSTNUM=0
AND ACCOUNTTYPE IN ('A','B','C','D')
AND (DEPT<70 OR DEPT IN (76,78))
GROUP BY CUSTNUM,STORENUM
ORDER BY CUSTNUM,FAV DESC) As T
Group by T.CustNum, T.Storenum
Avatar of trbbhm

ASKER

Not exactly working as I had hoped.  Here are the results that the SQL statement pulled:

          10        4             5
          47        1             2
          47        3             1
          47        4             1
          56        3             1
          56        4             1
          65        3             3
          83        3             2
          83        4             4
         109        3             1
         109        4             1
         136        3             2
         136        4             1
upload a sample db with the table
Is this MSSQL or Access, since you have put it in both topic areas? With MSSQL you can do like so:
Select T.CustNum, T.Storenum, T.Fav
FROM
(SELECT CUSTNUM, STORENUM, COUNT(STORENUM) AS FAV FROM CUSTSLS,CUSTOMER,
 rn=row_number() over (partition by custnum, storenum order by count(storenum) desc)
WHERE CUSTOMER.CUSTOMERNUM=CUSTSLS.CUSTNUM
AND NOT (CUSTNUM IN (1,2,3,4)
AND CUSTSLS.FORMAT='D'
AND MASTERCUSTNUM=0
AND ACCOUNTTYPE IN ('A','B','C','D')
AND (DEPT<70 OR DEPT IN (76,78))
GROUP BY CUSTNUM,STORENUM
) As T
where rn = 1

Open in new window

Avatar of trbbhm

ASKER

@Capricorn:  Not really sure how to do that.  Sry.  I'm running a Pervasive SQL DB and I can't just shoot up a BTR file.

Sry.
ASKER CERTIFIED SOLUTION
Avatar of ralmada
ralmada
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial