SQL select statement question

[jroth]

My dataset has 3 columns: customer id, store location, charges

I'd like to write a single select statment that will show each customer id
once, with the store location where they have the most charges, and the sum
of the charges for that store location.

This gives the max charges for each customer, but doesn't give the location:

select a.custID, max(new.amt) from smallchg a inner join;
(select custID, location, sum(charges) as amt from smallchg group by custID,
location) as new ;
on a.custID= new.CustID group by a.CustID

thanks.

joel

 

[KARL DEWEY]

Try this --
SELECT TOP 1 [customer id], [store location], Sum([charges]) as amt
FROM smallchg
GROUP BY custID, location
ORDER BY Sum([charges]) DESC;

 

[jroth]

Karl:
This just give me the customer with the most charges and their location. I
need a line for each customer, the location where they had the mosts charges,
and the sum of charges at that location.

thanks.

joel

Try this --
SELECT TOP 1 [customer id], [store location], Sum([charges]) as amt
FROM smallchg
GROUP BY custID, location
ORDER BY Sum([charges]) DESC;

My dataset has 3 columns: customer id, store location, charges

[quoted text clipped - 14 lines]

.

 

[Sylvain Lafontaine]

Use it as two subqueries, one for the maxAmt and the other for the location:

Select t1.custId,

(Select Top 1 sum(charges) as Amt from #t t2 where t2.custId = t1.custId
group by custId, location Order by sum(charges) desc) as maxAmt,

(Select Top 1 location from #t t2 where t2.custId = t1.custId
group by custId, location Order by sum(charges) desc) as location

from #t t1
Group By t1.CustId


The other solution would be to repeat your first subquery and join it to the
first result that you have got:

select a2.*, new2.*
From
(select a.custId, max (new.amt) as maxAmt from #t a inner join
(select custId, location, sum(charges) as amt from #t
group by custId, location) as new

on a.custId = new.CustId
group by a.custId

) as a2 inner join (select custId, location, sum(charges) as amt from #t
group by custId, location) as new2 on a2.custId = new2.custId

Where a2.maxAmt = new2.amt


I'm not that familiar with Access but probably that you can make many other
variations on that theme; especially by using the functions First().

Finally, an easy solution would be to simply use a temporary table to build
your result. People often forget that using an intermediary table to
compute a result is often the simpler and easiest way to achieve a result.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

Karl:
This just give me the customer with the most charges and their location. I
need a line for each customer, the location where they had the mosts
charges,
and the sum of charges at that location.

thanks.

joel

Try this --
SELECT TOP 1 [customer id], [store location], Sum([charges]) as amt
FROM smallchg
GROUP BY custID, location
ORDER BY Sum([charges]) DESC;

My dataset has 3 columns: customer id, store location, charges

[quoted text clipped - 14 lines]

.

 

[jroth via AccessMonster.com]

Sylvain-
Thank you very much.

joel
------------------

Use it as two subqueries, one for the maxAmt and the other for the location:

snip