SQL Server Forums
Profile | Register | 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
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ppatel112
Starting Member

26 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
2168 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
52249 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  
 New 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.05 seconds. Powered By: Snitz Forums 2000