Prev: nesting queries - best practice?
Next: IIf statement
From: jroth on 14 Jan 2010 17:09
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

From: KARL DEWEY on 14 Jan 2010 19:45
Try this --
SELECT TOP 1 [customer id], [store location], Sum([charges]) as amt
FROM smallchg
GROUP BY custID, location
ORDER BY Sum([charges]) DESC;

--
Build a little, test a little.


"jroth" wrote:

> 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
>
> .
>
From: jroth on 14 Jan 2010 22:26
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

KARL DEWEY wrote:
>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]
>>
>> .

From: Sylvain Lafontaine on 15 Jan 2010 00:25
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)


"jroth" <u57497(a)uwe> wrote in message news:a220cade2d447(a)uwe...
> 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
>
> KARL DEWEY wrote:
>>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]
>>>
>>> .
>


From: jroth via AccessMonster.com on 15 Jan 2010 10:56
Sylvain-
Thank you very much.

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

Sylvain Lafontaine wrote:
>Use it as two subqueries, one for the maxAmt and the other for the location:
snip

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201001/1

 | 
Pages: 1
Prev: nesting queries - best practice?
Next: IIf statement