adding a sum in query

[tonyvee1973]

Hi ALL
I have a query which i need to add a new aggregate in but going around in circles on where to add it in the query.
The query is below:

SELECT dbo.SL_ACCOUNTS.CUCODE,
dbo.SL_ACCOUNTS.CUCODE AS HeadOfficeAccount,
dbo.SL_ACCOUNTS.CUNAME,
dbo.ps_addresses.AddressLine1,
dbo.ps_addresses.AddressLine2,
dbo.ps_addresses.AddressLine3,
dbo.ps_addresses.AddressLine4,
dbo.ps_addresses.AddressLine5,
dbo.SL_ACCOUNTS.CUPHONE,
dbo.SL_ACCOUNTS.CUFAX,
dbo.SL_ACCOUNTS.CU_TOT_DISC,
dbo.SL_ACCOUNTS.CU_SETT_DISC_1,
dbo.SL_ACCOUNTS.CU_CREDIT_LIMIT,
'' AS DaysForNextVisit,
'' AS CompanyRegNumber,
'' AS BankName,
'' AS BankAddress1,
'' AS BankAddress2,
'' AS BankTown,
'' AS BankCounty,
'' AS BankPostCode,
'' AS BankTelephone,
'' AS BankAccount,
'' AS BankSortCode,
'' AS ProprietorsName,
'' AS ProprietorsAddress1,
'' AS ProprietorsAddress2,
'' AS ProprietorsTown,
'' AS ProprietorsCounty,
'' AS ProprietorsPostCode,
'' AS ProprietorsTelephone,
dbo.SL_ACCOUNTS.CUCONTACT,
REPLACE(REPLACE(dbo.SL_ACCOUNTS.CU_ON_STOP, '0', 'N'), '1', 'Y') AS CU_ON_STOP,
dbo.SL_ACCOUNTS.CU_PRICE_KEY AS PricelistNumber,
dbo.SL_ACCOUNTS.CUUSER1,
dbo.SL_ACCOUNTS.CUTURNOVERPTD,
dbo.SL_ACCOUNTS.CUTURNOVERYTD,
[highlight #FCE94F]'' AS LastYearSales[/highlight],
ROUND(dbo.SL_ACCOUNTS.CUBALANCE,2) AS CUBALANCE,
'' AS Notes1,
'' AS Notes2,
dbo.SL_ACCOUNTS.CUCURRENCYCODE,
'' AS Tax,
'' AS ConfirmOrders,
'' AS GenericCode3,
'' AS GenericCode4,
REPLACE(CONVERT(varchar(255),dbo.SL_ACCOUNTS.CU_NOTES), CHAR(13), ' ') AS Freetype1,
'' AS Freetype2,
dbo.SL_ACCOUNTS.CU_EMAIL,
dbo.SL_ACCOUNTS.CUUSER2 AS PriceCode,
'' AS DiscountCode,
dbo.SL_ACCOUNTS.CUAGED_1 AS CurrentMonthBalance,
dbo.SL_ACCOUNTS.CUAGED_2 AS [Month+1],
dbo.SL_ACCOUNTS.CUAGED_3 AS [Month+2],
dbo.SL_ACCOUNTS.CUAGED_4 AS [Month+3],
CONVERT(CHAR(8), SL_ACCOUNTS.CU_DATE_INV, 112) AS DateLastInvoice,
SLDetails.ST_NETT AS AmountLastInvoice,
CONVERT(CHAR(8), SL_ACCOUNTS.CU_DATE_PAY, 112) As DateLastPayment,
'' AS AmountLastPayment,
'' AS MinOrderValue,
dbo.SL_ACCOUNTS2.CU_FLAG1 AS PORefRequired,
'' AS NoteDateRequired,
'' AS PostOrderDiscount, 'N' AS UsrBreakDisc, '' AS JournalGroupCode,
'' AS TaxRegion,
'' AS DefaultLocationCode,
'' AS DefaultGeneric18,
'' AS DefaultGeneric19,
'' AS DefaultGeneric20,
'' AS DefaultGeneric21,
'' AS DefaultGeneric22,
'' AS DefaultGeneric23,
dbo.SL_ACCOUNTS2.CU_USRFLAG2
FROM dbo.SL_ACCOUNTS
INNER JOIN
dbo.ps_addresses ON dbo.SL_ACCOUNTS.CUCODE = dbo.ps_addresses.AccountNumber
INNER JOIN
dbo.SL_ACCOUNTS2 ON dbo.SL_ACCOUNTS.CUCODE = dbo.SL_ACCOUNTS2.CUCODE2
LEFT JOIN (
Select ST_COPYCUST,
ST_NETT,
ST_DATE_PUTIN
From (
Select SL_TRANSACTIONS.ST_COPYCUST,
SL_TRANSACTIONS.ST_NETT,
SL_TRANSACTIONS.ST_DATE_PUTIN,
Row_Number() Over (Partition By ST_COPYCUST Order By ST_DATE_PUTIN desc) As RowId
From SL_TRANSACTIONS
where st_trantype = 'INV'
) As ALL_SL_DETAILS
Where RowId = 1
) As sldetails
On dbo.SL_ACCOUNTS.CUCODE = sldetails.ST_COPYCUST

In the query above you will see a highlighted line for LastYearSales - i need to combine several fields together as they are in months then add the total in the 'LastYearSales' field in the query above.
I got the simple query together below to show what i need but am stuck in getting it apply to the main query above.

SELECT cucode,
SUM(cuturnovr_l1 + cuturnovr_l2 + cuturnovr_l3 + cuturnovr_l4 + cuturnovr_l5 + cuturnovr_l6 + cuturnovr_l7 + cuturnovr_l8 + cuturnovr_l9 + cuturnovr_l10 + cuturnovr_l11 + cuturnovr_l12) as LYS
FROM sl_accounts
group by cucode

Any help really would be appreciated

 

[drlex]

Can you not put it in a sub-query as you have done for "sldetails" and then join (equi- or left-) to the main query on field "cucode"?

soi là, soi carré

 

[tonyvee1973]

i tried but not sure where to put this sub query

 

[drlex]

Given that your query already has sub-queries at the end, that would seem the best place for it. Using an equi-join as the sub-query is a summary of your main table.

SELECT [i][b]<query - cut for readability>[/b][/i]
FROM dbo.SL_ACCOUNTS 
INNER JOIN
 dbo.ps_addresses ON dbo.SL_ACCOUNTS.CUCODE = dbo.ps_addresses.AccountNumber
 INNER JOIN
 dbo.SL_ACCOUNTS2 ON dbo.SL_ACCOUNTS.CUCODE = dbo.SL_ACCOUNTS2.CUCODE2
 LEFT JOIN (
[b][i]<existing sub-query - cut for readability>[/i][/b]
 ) As sldetails
 On dbo.SL_ACCOUNTS.CUCODE = sldetails.ST_COPYCUST[b][red]
INNER JOIN
(SELECT cucode,
 SUM(cuturnovr_l1 + cuturnovr_l2 + cuturnovr_l3 + cuturnovr_l4 + cuturnovr_l5 + cuturnovr_l6 + cuturnovr_l7 + cuturnovr_l8 + cuturnovr_l9 + cuturnovr_l10 + cuturnovr_l11 + cuturnovr_l12) as LYS
 FROM sl_accounts
 group by cucode) as LastYearSales
on dbo.SL_ACCOUNTS.CUCODE = LastYearSales.cucode
[/red][/b]

and you can then reference the figure as LastYearSales.LYS

capisce?



soi là, soi carré

 

[tonyvee1973]

Spot on the button Drlex, thanks buddy!

 

[gmmastros]

Tony,

Do you understand how this works? I use this technique all the time. I call it a derived table. It's important that you understand this because it is useful in many different situations.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[tonyvee1973]

Hi
Yes, i understand what its doing and the benefits (As need no huge groupings etc) but i just get aslightly confused on the mechanics - Ie i get the brackets in the wrong place etc. <Signing up for a SQL course>

 

[gmmastros]

Let me try to explain it.

You have a relatively small query, like this:

SELECT cucode,
       SUM(~Bunch Of Columns~) as LYS
FROM sl_accounts
group by cucode

Now you want to use the results of that query in another query. Think of it this way, if you had a table named LastYearSales with cucode, and LYS, then it would be easy to incorporate this in to your query. Ex:

Select  ~Columns~,
        LastYearSales.LYS
From    SomeTable
        Inner Join LastYearSales
          On SomeTable.cucode = LastYearSales.cucode

Simple, right? Unfortunately, you don't have a table named LastYearSales, you only have a query. Here's how you can use the query exactly as though it were a table.

Select  ~Columns~,
        [green][b]LastYearSales[/b][/green].LYS
From    SomeTable
        Inner Join [!]([/!]
            SELECT cucode,
                   SUM(~Bunch Of Columns~) as LYS
            FROM sl_accounts
            group by cucode
          [!])[/!] [blue][b]As LastYearSales[/b][/blue]
          On SomeTable.cucode = [green][b]LastYearSales[/b][/green].cucode

Notice a couple things.

(red) I added parenthesis around the query.
(blue) I gave the query an alias
(green) I use the alias throughout the outer query.
Also notice that the query within the parenthesis is completely unchanged.

Believe it or not, that's all there is to it. Basically, create a small query to return the data you need. This query should return a column to join on, and the data you want. Then, use parenthesis around the query, give it an alias, and use the alias consistently throughout the larger query.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[tonyvee1973]

Thanks gmmastros, appreciate the time you show in helping me to understand. i have made a note of this and will go home and try a few examples to get the hang of it.
Thanks