| Author |
 Topic  |
|
|
ppatel112
Starting Member
19 Posts |
 Posted - 03/10/2013 : 21:28:48
|
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
1390 Posts |
Posted - 03/11/2013 : 03:08:46
|
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 |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 03/11/2013 : 07:17:01
|
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/
|
|
|
| |
Topic |
|
|
|
software adds comp_companyid that distors my group
