[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 203
  • Last Modified:

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
0
pepps11976
Asked:
pepps11976
  • 13
  • 10
  • 2
1 Solution
 
Richard QuadlingSenior Software DeverloperCommented:
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

0
 
Richard QuadlingSenior Software DeverloperCommented:
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?
0
 
pepps11976Author Commented:
Hi RQuadling

With the above code i get unable to parse query text incorrect syntax near ')'
0
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
Kevin CrossChief Technology OfficerCommented:
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*
0
 
pepps11976Author Commented:
It sort of seems right but it is not grouped by producr (ss_prod)

can this be added?
0
 
Kevin CrossChief Technology OfficerCommented:
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,
...
0
 
pepps11976Author Commented:
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
0
 
pepps11976Author Commented:
the idear is to have something like this

year            Total GBP          product
2009            100                    bikes
2009             200                   Wheels
0
 
Kevin CrossChief Technology OfficerCommented:
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.
0
 
pepps11976Author Commented:
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?
0
 
pepps11976Author Commented:
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
0
 
Kevin CrossChief Technology OfficerCommented:
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. ;)
0
 
Kevin CrossChief Technology OfficerCommented:
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.
0
 
pepps11976Author Commented:
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

0
 
pepps11976Author Commented:
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?
0
 
Kevin CrossChief Technology OfficerCommented:
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?
0
 
pepps11976Author Commented:
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.
0
 
Kevin CrossChief Technology OfficerCommented:
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.
0
 
Kevin CrossChief Technology OfficerCommented:
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.
0
 
pepps11976Author Commented:
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
0
 
Kevin CrossChief Technology OfficerCommented:
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!
0
 
pepps11976Author Commented:
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
0
 
pepps11976Author Commented:
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?
0
 
pepps11976Author Commented:
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?
0
 
Kevin CrossChief Technology OfficerCommented:
Yes, that would make sense. The YEAR() function is going to give you a calendar year. If you have a table that contains the start and end dates of a fiscal year, you can make the SQL query match the cube in that regard.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 13
  • 10
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now