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

please advise

Sergiy (3/11/2013)

---

You may try to use WITH ROLLUP option in your query.
It won't remove all the rows with CompanyID's from the final recordset, but will add 2 records to it"

YEAR CompanyID TotalSales
2019 NULL 21932.360
2020 NULL 46448.040

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
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

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]