SQL statement to sum 12 records for each customer : The Official Microsoft ASP.NET Forums

Reply

capjlp

Member

195 Points

172 Posts

SQL statement to sum 12 records for each customer

May 31, 2012 01:34 PM|LINK

Hi All

I am building a Grid View using some data. I have records for each customer of there energy usage for 13 to 14 months I want to get a yearly usage for each customer. So i only want to sum the first 12 records for each customer. I have two tables i am pulling from my customer table and my energy usage table. I have the sum working on all records but not sure how to make the sum only sum up the first 12 records for each customer.

Here is what i have.

SELECT dbo_tblEnergyHistory.CoopCode, dbo_tblCustomer.CustomerName, dbo_tblEnergyHistory.LocationID, dbo_tblCustomer.LMI, dbo_tblCustomer.Income, dbo_tblCustomer.Zip, dbo_tblCustomer.County, Sum(dbo_tblEnergyHistory.kWh_Usage) AS SumOfkWh_Usage, dbo_tblEnergyHistory.DataStatus
FROM dbo_tblCustomer INNER JOIN dbo_tblEnergyHistory ON (dbo_tblCustomer.CoopCode = dbo_tblEnergyHistory.CoopCode) AND (dbo_tblCustomer.LocationID = dbo_tblEnergyHistory.LocationID)
GROUP BY dbo_tblEnergyHistory.CoopCode, dbo_tblCustomer.CustomerName, dbo_tblEnergyHistory.LocationID, dbo_tblCustomer.LMI, dbo_tblCustomer.Income, dbo_tblCustomer.Zip, dbo_tblCustomer.County, dbo_tblEnergyHistory.DataStatus
HAVING (((dbo_tblEnergyHistory.DataStatus)="br"))
ORDER BY dbo_tblEnergyHistory.CoopCode, dbo_tblCustomer.CustomerName;

Appreciate the help...

Thanks

Reply

snh_algorith...

Member

260 Points

80 Posts

Re: SQL statement to sum 12 records for each customer

May 31, 2012 01:38 PM|LINK

it think u may use select top (12) rows.

Just try it.

Reply

capjlp

Member

195 Points

172 Posts

Re: SQL statement to sum 12 records for each customer

May 31, 2012 01:40 PM|LINK

Hi

I thought of that.. But if i do that it only gets teh top 12 customers

I have thousands of customers and they all have 12 to 14 months of usage in the history table so i need to sum up the first 12 records for all customers.

I think i am going to have to do 2 selects perhaps...

Edited by capjlp on May 31, 2012 01:41 PM

Reply

spapim

Contributor

2446 Points

368 Posts

Re: SQL statement to sum 12 records for each customer

May 31, 2012 03:18 PM|LINK

Hi,

Try:

with CTE as
(
    select
        CoopCode,
        LocationID,
        DataStatus
        kWh_Usage,
        ROW_NUMBER() OVER(PARTITION BY CoopCode, LocationID order by Data) AS RowNum
    from dbo_tblEnergyHistory
    where DataStatus = 'br'
)

SELECT
    c.CoopCode,
    c.CustomerName,
    c.LocationID,
    c.LMI,
    c.Income,
    c.Zip,
    c.County,
    Sum(e.kWh_Usage) AS SumOfkWh_Usage,
    e.DataStatus
FROM dbo_tblCustomer as c
INNER JOIN CTE as e ON (e.CoopCode = c.CoopCode) AND (e.LocationID = c.LocationID)
where e.RowNum <= 12
GROUP BY
    c.CoopCode,
    c.CustomerName,
    c.LocationID,
    c.LMI,
    c.Income,
    c.Zip,
    c.County,
    e.DataStatus
ORDER BY
    c.CoopCode,
    c.CustomerName

Hope this helps.

www.imoveisemexposicao.com.br/imoveis-venda-são_paulo-residencial
www.imobiliariasemsuzano.com.br