parthmeister (3/10/2013)
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
201921932.360
202046448.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
202011082.7201228
201912575.5201229
20202159.8501229
20207973.0501231
20201890.0001232
20199356.8401234
202010794.3101234
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
Have you tried this as your first query, in the hope that the software will just add Comp_CompanyId to the outer SELECT?
SELECT a.[YEAR] , a.[TotalSales]
FROM (
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
ORDER BY {you need to decide what order by you should be using to get the correct TOP 100}
) AS [a]
ORDER BY a.[Year],a.[TotalSales]
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);