SQL Query Grouping Data An a Sum Total

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

Select all Open in new window

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

Select all Open in new window

is what you want?

Hi RQuadling

With the above code i get unable to parse query text incorrect syntax near ')'

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*

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

can this be added?

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,
...

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

Select all Open in new window

however it still is not grouping on product ss_prod

the idear is to have something like this

year            Total GBP          product
2009            100                    bikes
2009             200                   Wheels

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.

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?

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

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. ;)

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.

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

Select all 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

Select all 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]

Select all Open in new window

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?

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?

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.

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.

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.

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

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!

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

Select all 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

Select all 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

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?

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?