Select the row with the max value from a table where sum is calculated

Question

I'm trying to find out how can I extract the rows with the highest value on Suma column from a table which I did with this script:

SELECT specializare, 
       Count(codprocedura) AS NrProceduri, 
       Sum(pret)           AS Suma 
FROM   (SELECT p.pret                AS Pret, 
               fp.specializaredoctor AS Specializare, 
               fp.codprocedura       AS CodProcedura 
        FROM   proceduri p 
               INNER JOIN fisepacienti fp 
                       ON fp.codpacienti = p.codproc)intermediar 
GROUP  BY intermediar.specializare

The DB tables can be created with this:

create table Pacienti( CodP int not null primary key, Nume varchar(50), Prenume varchar(50), Descriere varchar(50), Varsta int, DataNasterii varchar(50)

);

create table Proceduri( CodProc int not null primary key, Nume varchar(50), Descriere varchar(50), Pret int

);

create table FisePacienti( PRIMARY KEY(CodPacienti, CodProcedura), CodPacienti int FOREIGN KEY REFERENCES Pacienti(CodP), CodProcedura int FOREIGN KEY REFERENCES Proceduri(CodProc), Data varchar(50), NumeDoctor varchar(50), PrenumeDoctor varchar(50), SpecializareDoctor varchar(50), VechimeDoctor varchar(50)

);

Gordon Linoff Gordon Linoff · Accepted Answer · 2019-08-31T14:15:20

You can use TOP (1) WITH TIES:

SELECT TOP (1) WITH TIES fp.specializare, 
       Count(fp.codprocedura) AS NrProceduri, 
       Sum(p.pret) AS Suma
FROM proceduri p JOIN
     fisepacienti fp 
     ON fp.codpacienti = p.codproc
GROUP BY fp.specializare 
ORDER BY SumA DESC;

Note that a subquery is not needed for the aggregation.

Select the row with the max value from a table where sum is calculated

1 Answers