SQL query to Sum with Group By

Hello there,

I have this query and it is working perfect. Now I want to go one step further and have 2 columns which retunr the sales qty and discount to also have them SUM, but the problem is I have the territory column which is not letting me. Can somebody help me to have both in the query result.

1
My query is

SELECT
    dbo.SupplierProductDetail.companyName,
    dbo.FDOSales.ProductNameEng,
    dbo.FDOSales.SalesPrice,
    dbo.FDOSales.PurchasePrice,
     SUM(dbo.FDOSales.SalesQtyinclDis - dbo.FDOSales.SalesDiscount) AS SalesQtyInclDis,
    SUM(dbo.FDOSales.SalesDiscount)   AS SalesDiscount,
    dbo.FDOProvince.Province,
    dbo.FDOProvince.Branch
FROM
    dbo.FDOSales
INNER JOIN
    dbo.SupplierProductDetail
ON
    (
        dbo.FDOSales.ProductCode = dbo.SupplierProductDetail.ProductCode)
INNER JOIN
    dbo.FDOProvince
ON
    (
        dbo.FDOSales.ProvinceCode = dbo.FDOProvince.ProvinceCode)
WHERE
     SalesQtyinclDis > 0 AND 
    dbo.FDOSales.SaleDate >= '20151001'
AND dbo.FDOSales.SaleDate <= '20151031'
GROUP BY
    dbo.SupplierProductDetail.companyName,
    dbo.FDOSales.ProductNameEng,
    dbo.FDOSales.SalesPrice,
    dbo.FDOSales.PurchasePrice,
    dbo.FDOProvince.Province,
    dbo.FDOProvince.Branch
ORDER BY
    dbo.SupplierProductDetail.companyName ASC,
    dbo.FDOSales.ProductNameEng ASC,
    dbo.FDOProvince.Branch ASC,
    dbo.FDOProvince.Province ASC ;

Open in new window

zolfAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vikas GargBusiness Intelligence DeveloperCommented:
What is your current result ?

And what you want ?

Can you provide any sample ?
0
arnoldCommented:
If you get errors because one of the columns in the sum is null leading to an error, use isnull(column,0) for both.
Alternatively try sum (column1) - sum (column2)
0
zolfAuthor Commented:
Thanks for the feedbacks.

What is your current result ?
The current result i have shown in the screenshot

And what you want ?
I want to be able to Sum the cols SalesQtyinclDis and Disqty column(which I have shown in green in that screenshot) related to the other cols
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

zolfAuthor Commented:
here is the sample data in excel
1.xls
0
Mr KnackeredCommented:
Make the changes Arnold has suggested and then rerun and if still getting errors please post them so we can see what it's unhappy about.

If your amount columns allow null the this could be causing your issue. Using sum(isnull(amount,0)) will sum the data and if null is found it will treat it as zero.

I would also change your sum(field1 - field2) to sum(field1) - sum(field2). I would also move the two sum rows from the select part to the end so that your select list matches your group by list with the sums after. Just makes it a bit more readable. Very much personal preference on that one.
0
Anthony PerkinsCommented:
I want to be able to Sum the cols SalesQtyinclDis and Disqty column(which I have shown in green in that screenshot) related to the other cols
I am confused:
SalesQtyInclDis =   dbo.FDOSales.SalesQtyinclDis - dbo.FDOSales.SalesDiscount)
So if you add this column to dbo.FDOSales.SalesDiscount you end up with dbo.FDOSales.SalesQtyinclDis
0
zolfAuthor Commented:
EE please help me. DO I need to use subquery to get my requirements
0
Olaf DoschkeSoftware DeveloperCommented:
Aren't you already having the sum?

SUM(dbo.FDOSales.SalesQtyinclDis - dbo.FDOSales.SalesDiscount) AS SalesQtyInclDis,
SUM(dbo.FDOSales.SalesDiscount)   AS SalesDiscount

The first one is already summing SalesQtyinclDis-SalesDiscount

To see this, query the simple sum of  SalesQtyinclDis, too:

SUM(dbo.FDOSales.SalesQtyinclDis - dbo.FDOSales.SalesDiscount) AS Total,
SUM(dbo.FDOSales.SalesDiscount)   AS SumSalesDiscount
SUM(dbo.FDOSales.SalesQtyinclDis) AS SumSalesQtyInclDis

If you need to aggregate this further, then you need to group by less columns, which makes groups larger and so sums more into one records.

Bye, Olaf.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
EE please help me.
Unfortunately if you ignore questions and/or don't explain it fully we cannot help you,  Put it another way: Help us help you.
0
zolfAuthor Commented:
my apologies, maybe I did not explain my requirement properly.

I dont want to sum those two columns together,instead I want to sum the salesqty and disqty for each of those products.
You see, I have Many Suppliers and its products which gets sold in different States(Province Col) by the branch offices(Branch Col). At present these columns are showing the salesQty and the discountQty of each product sold in each province and branch. I know that if I remove these columns then I get the sum of them. but I want to know what is the way out to get them together in a report.

cheers
Zolf
0
arnoldCommented:
Having a sample of two to five rows of data that you post also post the result you expect to get.

You keep using the term sum which it seems is not what you want.

Are you trying to add up items sold based on supplier, province, etc,
supplierA, provinceA, count
supplierA,provinceB,count2


You also need to provide the context of the columns or their naming scheme reflects their meaning.
you have items sold at full price and some sold at a discount. what are you looking to do/get there?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.