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)

);

Sample data and expected results will help us help you.

Thom A
– Thom A ♦

2019-08-31 13:58:21 +00:00
Commented Aug 31, 2019 at 13:58 — Thom A
– Thom A ♦, Commented Aug 31, 2019 at 13:58

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

2

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.

answered Aug 31, 2019 at 14:15

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

johnnnnn Over a year ago

Gordon, thanks a lot! I didn't have any idea that I can use TOP() with this function " WITH TIES". It saves lives. I used initially only TOP(1) but it has not covered all the cases requested.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related