Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 software adds comp_companyid that distors my group
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ppatel112
Starting Member

35 Posts

Posted - 03/10/2013 :  21:28:48  Show Profile  Reply with Quote
Hi Folks,

i am having the sql below:
SELECT TOP (100) SAMINC.dbo.OESTATS.YR as YEAR , SUM(SAMINC.dbo.OESTATS.SALESAMTF) AS TotalSales
FROM SAMINC.dbo.OESTATS INNER JOIN
dbo.Company ON SAMINC.dbo.OESTATS.LINVCUST = dbo.Company.Comp_IdCust COLLATE Latin1_General_BIN
GROUP BY SAMINC.dbo.OESTATS.YR
that generated the results below:

YEAR TotalSales
2019 21932.360
2020 46448.040

it is find in SQL, the software that i am using automatically adds a field called comp_companyid to it so the sql becomes

SELECT TOP (100) SAMINC.dbo.OESTATS.YR AS YEAR, SUM(SAMINC.dbo.OESTATS.SALESAMTF) AS TotalSales, dbo.Company.Comp_CompanyId
FROM SAMINC.dbo.OESTATS INNER JOIN
dbo.Company ON SAMINC.dbo.OESTATS.LINVCUST = dbo.Company.Comp_IdCust COLLATE Latin1_General_BIN
GROUP BY SAMINC.dbo.OESTATS.YR, dbo.Company.Comp_CompanyId

and the results are diffrent because of it as below:

YEAR TOTALSales comp_companyid
2020 11082.720 1228
2019 12575.520 1229
2020 2159.850 1229
2020 7973.050 1231
2020 1890.000 1232
2019 9356.840 1234
2020 10794.310 1234

as it is a requirement to add the comp_companyid it completely distors my results that i am looking for.

any way to acheive the first result by tweaking the second tsql?

please advise

bandi
Flowing Fount of Yak Knowledge

India
2241 Posts

Posted - 03/11/2013 :  03:08:46  Show Profile  Reply with Quote
SELECT DISTINCT SAMINC.dbo.OESTATS.YR AS YEAR, SUM(SAMINC.dbo.OESTATS.SALESAMTF) OVER(PARTITION BY SAMINC.dbo.OESTATS.YR, dbo.Company.Comp_CompanyId) AS TotalSales, dbo.Company.Comp_CompanyId
FROM SAMINC.dbo.OESTATS INNER JOIN
dbo.Company ON SAMINC.dbo.OESTATS.LINVCUST = dbo.Company.Comp_IdCust COLLATE Latin1_General_BIN



--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 03/11/2013 :  07:17:01  Show Profile  Reply with Quote
quote:
Originally posted by bandi

SELECT DISTINCT SAMINC.dbo.OESTATS.YR AS YEAR, SUM(SAMINC.dbo.OESTATS.SALESAMTF) OVER(PARTITION BY SAMINC.dbo.OESTATS.YR, dbo.Company.Comp_CompanyId) AS TotalSales, dbo.Company.Comp_CompanyId
FROM SAMINC.dbo.OESTATS INNER JOIN
dbo.Company ON SAMINC.dbo.OESTATS.LINVCUST = dbo.Company.Comp_IdCust COLLATE Latin1_General_BIN



--
Chandu



it should be


SELECT DISTINCT SAMINC.dbo.OESTATS.YR AS YEAR, SUM(SAMINC.dbo.OESTATS.SALESAMTF) OVER(PARTITION BY SAMINC.dbo.OESTATS.YR) AS TotalSales, 
dbo.Company.Comp_CompanyId
FROM SAMINC.dbo.OESTATS INNER JOIN
dbo.Company ON SAMINC.dbo.OESTATS.LINVCUST = dbo.Company.Comp_IdCust COLLATE Latin1_General_BIN


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000