troubleshooting Question

SQL Query Grouping Data An a Sum Total

Avatar of pepps11976
pepps11976 asked on
Microsoft SQL Server 2008
25 Comments1 Solution229 ViewsLast Modified:
Hi Everyone

I have the following Code
SELECT     TOP (100) PERCENT (CASE WHEN dbo.itran.it_fcrate = 0 THEN (dbo.itran.it_quan * dbo.itran.it_price / 100) 
                      ELSE (dbo.itran.it_quan * dbo.itran.it_price / dbo.itran.it_fcrate / 100) END) AS it_total_gbp, dbo.itran.it_doc, dbo.ihead.ih_orddate, dbo.ssale.ss_acode, 
                      LEFT(dbo.itran.it_anal, 3) AS group_anal, dbo.itran.it_anal, dbo.ssale.ss_adesc, dbo.sname.sn_account, dbo.ihead.ih_doc AS iheaddocnumber, 
                      dbo.[Anal Code].ss_prod
FROM         dbo.itran LEFT OUTER JOIN
                      dbo.ssale INNER JOIN
                      dbo.[Anal Code] ON dbo.ssale.ss_acode = dbo.[Anal Code].ss_acode ON dbo.itran.it_anal = dbo.ssale.ss_acode RIGHT OUTER JOIN
                      dbo.ihead INNER JOIN
                      dbo.slook ON dbo.ihead.ih_terrtry = dbo.slook.lk_code LEFT OUTER JOIN
                      dbo.sname ON dbo.ihead.ih_account = dbo.sname.sn_account ON dbo.itran.it_doc = dbo.ihead.ih_doc
WHERE     (dbo.itran.it_status = 'A') AND (dbo.itran.it_anal <= 'ZZZ') AND (dbo.itran.it_doc <= 'WOR') AND (dbo.ihead.ih_sorder <> ' ') AND (dbo.ssale.ss_acode IS NOT NULL)
ORDER BY dbo.ihead.ih_account

what it does is tell me all orders that have been placed on the system and gives me the GBP amount (it_total_gbp) for that order, what i would like to do is group the data first by the Year (ih_orddate) then by the Product (ss_prod) then i would like the Total GBP Value (it_total_gbp) for the years. Then after i have done that i am assuming i could add another column where i could place the forcasted amount in for each product for the comming financial year?

Could somebody help me to acheive this?

Join our community to see this answer!
Unlock 1 Answer and 25 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 25 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros