Help with SELECT statement

8 Sep 2006 7:28 PM

David

Ok, I've got two tables, Clients and History. The relevant fields look
like this:

Clients
-----------
ClientID
AccountNo

History
-----------
AccountNo
Year
Value

Each ClientID can have one or more accounts, so multiple records in the
clients table for each clientid. Each account will have a record for
several different years in history. I need to get the sum of "value"
from history by client and by year.

Clients
-----------
"A", "123"
"A", "456"

History
-----------
"123", 2004, 100
"123", 2005, 125
"123", 2006, 150
"456", 2004, 50
"456", 2005, 75
"456", 2006, 100

So, I want to get the total value per client, per year, so if I feed it
ClientID "A", I should get the results:

2004, 150
2005, 200
2006, 250

Any help is greatly appreciated.

8 Sep 2006 7:34 PM

Aaron Bertrand [SQL Server MVP]

DECLARE @ClientID CHAR(1);

SET @ClientID = 'A';

SELECT
    h.[Year],
    SUM(h.[Value])
FROM
    dbo.Clients c
INNER JOIN
    dbo.History h
ON
    c.AccountNo = h.AccountNo
WHERE
    c.ClientID = @ClientID
GROUP BY
    h.[Year]
ORDER BY
    h.[Year];

Show quote

"David" <da***@adrenasys.com> wrote in message
news:1157743693.232395.307490@i42g2000cwa.googlegroups.com...
> Ok, I've got two tables, Clients and History. The relevant fields look
> like this:
>
> Clients
> -----------
> ClientID
> AccountNo
>
> History
> -----------
> AccountNo
> Year
> Value
>
> Each ClientID can have one or more accounts, so multiple records in the
> clients table for each clientid. Each account will have a record for
> several different years in history. I need to get the sum of "value"
> from history by client and by year.
>
> Clients
> -----------
> "A", "123"
> "A", "456"
>
> History
> -----------
> "123", 2004, 100
> "123", 2005, 125
> "123", 2006, 150
> "456", 2004, 50
> "456", 2005, 75
> "456", 2006, 100
>
> So, I want to get the total value per client, per year, so if I feed it
> ClientID "A", I should get the results:
>
> 2004, 150
> 2005, 200
> 2006, 250
>
> Any help is greatly appreciated.
>

8 Sep 2006 8:17 PM

David

As usual, I was putting WAY too much thought in to it. That works
perfectly! Thanks Aaron!

Aaron Bertrand [SQL Server MVP] wrote:
Show quote

> DECLARE @ClientID CHAR(1);
>
> SET @ClientID = 'A';
>
> SELECT
>     h.[Year],
>     SUM(h.[Value])
> FROM
>     dbo.Clients c
> INNER JOIN
>     dbo.History h
> ON
>     c.AccountNo = h.AccountNo
> WHERE
>     c.ClientID = @ClientID
> GROUP BY
>     h.[Year]
> ORDER BY
>     h.[Year];
>
>
> "David" <da***@adrenasys.com> wrote in message
> news:1157743693.232395.307490@i42g2000cwa.googlegroups.com...
> > Ok, I've got two tables, Clients and History. The relevant fields look
> > like this:
> >
> > Clients
> > -----------
> > ClientID
> > AccountNo
> >
> > History
> > -----------
> > AccountNo
> > Year
> > Value
> >
> > Each ClientID can have one or more accounts, so multiple records in the
> > clients table for each clientid. Each account will have a record for
> > several different years in history. I need to get the sum of "value"
> > from history by client and by year.
> >
> > Clients
> > -----------
> > "A", "123"
> > "A", "456"
> >
> > History
> > -----------
> > "123", 2004, 100
> > "123", 2005, 125
> > "123", 2006, 150
> > "456", 2004, 50
> > "456", 2005, 75
> > "456", 2006, 100
> >
> > So, I want to get the total value per client, per year, so if I feed it
> > ClientID "A", I should get the results:
> >
> > 2004, 150
> > 2005, 200
> > 2006, 250
> >
> > Any help is greatly appreciated.
> >