Avatar of pepps11976
pepps11976
 asked on

SQL Query Grouping Data An a Sum Total

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

Open in new window


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?

John
Microsoft SQL Server 2008

Avatar of undefined
Last Comment
Kevin Cross

8/22/2022 - Mon
Richard Quadling

How does this work so far?

SELECT TOP (100) PERCENT
	YEAR(ih_orddate) [Year],
	SUM
		(
		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 [Total it_total_gbp]
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)

GROUP BY
	YEAR(ih_orddate),
	ss_prod

ORDER BY
	dbo.ihead.ih_account

Open in new window

Richard Quadling

I wonder if
SELECT TOP (100) PERCENT
	YEAR(ih_orddate) [Year],
	SUM
		(
		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 [Total it_total_gbp],
	SUM
		(
		CASE
			WHEN dbo.itran.it_fcrate = 0 THEN
				0
	        	ELSE
	        		(dbo.itran.it_quan * dbo.itran.it_price / dbo.itran.it_fcrate / 100)
	        	END)
		) AS [Total forecast]

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)

GROUP BY
	YEAR(ih_orddate),
	ss_prod

ORDER BY
	dbo.ihead.ih_account

Open in new window

is what you want?
pepps11976

ASKER
Hi RQuadling

With the above code i get unable to parse query text incorrect syntax near ')'
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Kevin Cross

The extra ) is after the END on lines 10 and 19. You probably want to include ss_prod in the SELECT also, pending the rest is what you needed. *smile*
pepps11976

ASKER
It sort of seems right but it is not grouped by producr (ss_prod)

can this be added?
Kevin Cross

RQuadling does have a GROUP BY there:
...
GROUP BY
      YEAR(ih_orddate),
     ss_prod
...

What is missing is that ss_prod is NOT in the SELECT portion of the query; therefore, you will need to add that to the SELECT. That is what I meant above; sorry for not being clear. It should look like this:
SELECT TOP (100) PERCENT
      YEAR(ih_orddate) [Year],
     ss_prod,
...
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
pepps11976

ASKER
ok this is now my new code
SELECT     TOP (100) PERCENT YEAR(dbo.ihead.ih_orddate) AS Year, SUM(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 [Total it_total_gbp], 
                      SUM(CASE WHEN dbo.itran.it_fcrate = 0 THEN 0 ELSE (dbo.itran.it_quan * dbo.itran.it_price / dbo.itran.it_fcrate / 100) END) AS [Total forecast], 
                      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)
GROUP BY YEAR(dbo.ihead.ih_orddate), dbo.[Anal Code].ss_prod, dbo.ihead.ih_account
ORDER BY dbo.ihead.ih_account

Open in new window


however it still is not grouping on product ss_prod
pepps11976

ASKER
the idear is to have something like this

year            Total GBP          product
2009            100                    bikes
2009             200                   Wheels
Kevin Cross

How is dbo.ihead.ih_account involved as I do not see it in your SELECT, but you are GROUPing and ORDERing by it; therefore, you will have multiple lines for a given year and product if you have different accounts for that year and product combination. Removing that may give you what you need if you are currently not getting it.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
pepps11976

ASKER
ok you were right about the ih_account thing it all seems to work would there be any reason why it would be giving me the wrong total sums?
pepps11976

ASKER
also can you explain what the below code is for

SUM(CASE WHEN dbo.itran.it_fcrate = 0 THEN 0 ELSE (dbo.itran.it_quan * dbo.itran.it_price / dbo.itran.it_fcrate / 100) END) AS [Total forecast],
                      dbo.[Anal Code].ss_prod

John
Kevin Cross

Okay, take a look at these again, maybe they need adjusting:

SUM(
   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 [Total it_total_gbp],
SUM(
   CASE
      WHEN dbo.itran.it_fcrate = 0
         THEN 0
      ELSE (dbo.itran.it_quan * dbo.itran.it_price / dbo.itran.it_fcrate / 100)
   END
) AS [Total forecast]

I have highlighted things to check. Ensure the logic around it_fcrate is correct, i.e., the TRUE|FALSE actions based on it_fcrate = 0 is correct. I suspect it is; however, it does hurt to double check.

The next highlighted spot is next to check if the it_fcrate condition is correct. I highlighted the it_fcrate / 100 bit as SQL does integer division on integer values; therefore, what may be happening is that your result is losing partial responses as such altering final calculation. if all the values are integer, my suspicions that is the cause increases. ;)
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Kevin Cross

As far as what that code is, that may be the attempt at forecast. I am not sure that is what you need, though. In my head, when I hear forecast with financial data, I think of the trend over the years in chronological order and then making a projection based on the slope, i.e., the average percent change over time. Then simply multiple your last year's total GBP * 1 + {average increase calculated}. Just a quick thought anyway...

Let's resolve the calc first I guess, then maybe I will have a better read and you can clarify what you intend to do for the forecast calculation.
pepps11976

ASKER
I dont fully understand at the moment i have a view called order Value which looks like this
 
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.ihead.ih_account, dbo.ihead.ih_name, dbo.itran.it_doc, 
                      dbo.ihead.ih_orddate, dbo.ihead.ih_sorder, dbo.ihead.ih_terrtry, dbo.slook.lk_desc, 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
FROM         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 LEFT OUTER JOIN
                      dbo.ssale RIGHT OUTER JOIN
                      dbo.itran ON dbo.ssale.ss_acode = dbo.itran.it_anal ON dbo.ihead.ih_doc = dbo.itran.it_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

Open in new window


all of the result here are correct i have checked so i know that the it_total GBP values are all ok.

All i want to do now is to just total the values for the diffrent products, so given that i am only using sum surely this would be correct

this code is exactly what i want
SELECT     TOP (100) PERCENT YEAR(dbo.ihead.ih_orddate) AS Year, SUM(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 [Total it_total_gbp], 
                      SUM(CASE WHEN dbo.itran.it_fcrate = 0 THEN 0 ELSE (dbo.itran.it_quan * dbo.itran.it_price / dbo.itran.it_fcrate / 100) END) AS [Total forecast], 
                      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)
GROUP BY YEAR(dbo.ihead.ih_orddate), dbo.[Anal Code].ss_prod
ORDER BY year

Open in new window

 but it shows the wrong values when i compare to a graph i created based on the Order value view which i mentioned above.

also i am still unsure as to what this part of the code is for
SUM(CASE WHEN dbo.itran.it_fcrate = 0 THEN 0 ELSE (dbo.itran.it_quan * dbo.itran.it_price / dbo.itran.it_fcrate / 100) END) AS [Total forecast]

Open in new window

pepps11976

ASKER
hi mwvisa1

not sure if you got my above post but given what i said there surley that would prove that the calc is correct?
Your help has saved me hundreds of hours of internet surfing.
fblack61
Kevin Cross

Not exactly. The [Total it_total_gbp] in the original view is for individual dates and accounts. If you then GROUP BY year and product, I would expect the numbers to be different; however, you would know your data and what you expect than any of us, so you just need to look at the formula as I instructed and see if the conditions still apply when grouping by year and product. They may not.

As for the second part:
SUM(CASE WHEN dbo.itran.it_fcrate = 0 THEN 0 ELSE (dbo.itran.it_quan * dbo.itran.it_price / dbo.itran.it_fcrate / 100) END) AS [Total forecast]

I thought I explained that is probably RQuadling's attempt at answering your add a column to do forecast of next month. What the code is doing is only calculating the SUM() if it_fcrate <> 0. Not sure what the intent there is. If you want something different, put in the correct formula. As I said, it comes down to what is your definition of a forecast. I know what mine is working with a CPA as a boss; however, I do not know what your needs are with that regard, so would be steering you down the wrong path potentially. So I would make sure everything else works FIRST, then turn your attention to what you want to do for forecast.

Does that make sense now?
pepps11976

ASKER
sorry yes you did explain but i wrote the post befor i saw your reply.

its just i have created a cube Using SSAS that does exactly what i am after but i wanted to created the same thing in a view so that i could add forecasted data.

And the results just do not match the same SUM criteria etc applys in the cube.
Kevin Cross

Okay, so data you are comparing to is SSAS cube. Hmm. Stepping back, your original query does NOT include dbo.[Anal Code].ss_prod (by the way, interesting naming convention *smile*); therefore, I would check to ensure that the JOINs are correct. You have a number of them that are nested without () which may be causing confusion on maintenance if that is not what you intended. I will rewrite the FROM to be very clear and it might reveal the issue. After you get the JOINs rewritten, run a new detail and see if you are getting the correct number of rows and values compared to original query.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Kevin Cross

And clarification/correction, "original query" is the view shown in http:#36903724 as I see the code in the question does have ss_prod. Advice still stands, though, if the SSAS cube is based on the view code which works and you are adding ss_prod to new code which is not adding up to same.
pepps11976

ASKER
Thanks for the advice I will check this first thing in the morning, I am pretty new to SQL server can I ask what you mean when you say

 ' I would checkto see if your joins are correct you have a number of them nested without () '

John
Kevin Cross

Good luck. Specifically, the RIGHT OUTER JOIN and the splitting of ON from JOIN'd tables are what I would look at. Best to have:

table1
INNER JOIN table2 ON ...
LEFT JOIN table3 ON ...

Hope that helps!
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
pepps11976

ASKER
Hi  mwvisa1

Ok have double checked things and here are my findings

This is my Original Order Value view i have filtered it by a certain product i manually totaled up the it_total_gbp and compared to my cube data all added up correctly  
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.ihead.ih_account, dbo.ihead.ih_name, dbo.itran.it_doc, 
                      dbo.ihead.ih_orddate, dbo.ihead.ih_sorder, dbo.ihead.ih_terrtry, LEFT(dbo.itran.it_anal, 3) AS group_anal, dbo.itran.it_anal, dbo.ihead.ih_doc AS iheaddocnumber, 
                      dbo.[Anal Code].ss_prod
FROM         dbo.[Anal Code] INNER JOIN
                      dbo.itran ON dbo.[Anal Code].ss_acode = dbo.itran.it_anal RIGHT OUTER JOIN
                      dbo.ihead 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.[Anal Code].ss_prod = 'Components') AND (dbo.ihead.ih_orddate >= '07/01/2010') AND (dbo.ihead.ih_orddate <= '06/30/2011')
ORDER BY dbo.ihead.ih_account

Open in new window


and this is the view i am trying to create but using the group by clause

 
SELECT     TOP (100) PERCENT YEAR(dbo.ihead.ih_orddate) AS Year, SUM(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 [Total it_total_gbp], dbo.[Anal Code].ss_prod
FROM         dbo.[Anal Code] INNER JOIN
                      dbo.itran ON dbo.[Anal Code].ss_acode = dbo.itran.it_anal RIGHT OUTER JOIN
                      dbo.ihead 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.[Anal Code].ss_prod = 'Components') AND (dbo.ihead.ih_orddate >= '07/01/2010') AND (dbo.ihead.ih_orddate <= '06/30/2011')
GROUP BY YEAR(dbo.ihead.ih_orddate), dbo.[Anal Code].ss_prod
ORDER BY dbo.[Anal Code].ss_prod

Open in new window


these totals do not add up the same, i have checked my table joins and i am pretty sure they are ok.

can you offer any more advice?

John
pepps11976

ASKER
I have just had a thought within my cube i have a time dimension from which i have created a Fiscal year, because sql does not know about the fiscal year would that be the reason i am getting wrong results?
pepps11976

ASKER
Ok i can comfirm my suspisions were correct the reason was that my cube was dividing by fiscal year obviously SQL did not see that, so given we now no the reason can i still get my static data in?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Kevin Cross

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.