Help with sql view

Question

Help with sql view

Navigation

#1 by (0 votes)

0

I'm stuck in an exercise for class, the teacher has also tried to help me and has not finished leaving ... the statement is as follows:

Create a view, called top_patients, that collects the patient from each city that has spent more on visits (throughout the entire period). Specifically, a list of patients is requested, ordered by the expense stating the name, DNI, city and accumulated expense. (Find the information needed to create a view in PostgreSQL)

The case is that separately if you give me the results, that is, ID, and city, and then separately, the expense and the name, but I can not put it all together as one table ...

I also attach the description of the patient and visit tables, which are what are needed for the exercise:

PATIENT (Patient) The table contains the information about the patients. From these the patient number (Pat_Number, which is the primary key), the name (Name), the address (Address), its city (City), the national identification number (DNI) that is unique for each patient is stored. it can be null if it is not available, and finally the date of the last visit (Last_Visit).

VISIT (Visit) The table contains information about the visits patients make to the doctors. The following information is kept from each visit: the doctor's member number (Doc_Number), the patient number (Pat_Number), the date of visit (Visit_Date), the amount (Price), which can not be negative. The attribute trickle of the doctor's collegiate number (Doc_Number), the patient number (Pat_Number) and the date of the visit (Visit_Date) are the primary key of the table. The physician's number of the Doc_Number is a foreign key to MEDICAL (DOCTOR) and the patient number (Pat_Number) is a foreign key of PATIENT (PATIENT). There may be patients who have not made any visits and doctors who have not seen patients.

And this is the code that I've been carrying so far, to see if you can help me with something, thank you very much in advance! : D

set search_path to ubd_20151;

CREATE VIEW top_patients AS
select name, DNI
from Patient,(
select max("Despesa acumulada"), city  from (
select sum(price) as "Despesa acumulada", city, name, DNI
from visit, patient
where visit.Pat_Number = patient.Pat_Number
group by city, city, name, DNI
order by "Despesa acumulada") as inst
group by city) as inst2
group by name, DNI

sql postgresql

asked by THR4SH3RP0L0 02.02.2017 в 17:18

source

1 answer

Get some type of identifier for a mobile device with Ionic, ngCordova? How do I use a newly created merchant_order for the user to make his payment?

score 0 · Answer 1

It seems to me that you are asking for 2 different things. If what you ask is:

Create a view, called top_patients, that collects the patient from each city that has spent more on visits (throughout the entire period).

... then, this is one way to do it:

create view top_patients as
with patient_despesa_acumuladas as (
    select p.pat_number,
           coalesce(sum(v.price), 0) despesa_acumulada
      from patient p
      left join visit v
        on v.pat_number = p.pat_number
     group by p.pat_number)
select p.pat_number,
       p.name,
       p.address,
       p.city,
       p.dni,
       p.last_visit
  from (select p.*,
               row_number() over (
                 partition by p.city
                 order by pda.despesa_acumulada desc) as rn
          from patient p
          join patient_despesa_acumuladas pda
            on pda.pat_number = p.pat_number) p
 where p.rn = 1

But on the other hand, you also ask for the following, which does not seem to be the same:

Specifically, a list of the patients is requested, ordered by the expense in which the name, the DNI, the city and the accumulated expense are recorded.

In this case, the query could be:

create view top_patients as
with patient_despesa_acumuladas as (
    select p.pat_number,
           coalesce(sum(v.price), 0) despesa_acumulada
      from patient p
      left join visit v
        on v.pat_number = p.pat_number
     group by p.pat_number)
select p.name,
       p.dni,
       p.city,
       pda.despesa_acumulada
  from patient p
  join patient_despesa_acumuladas pda
    on pda.pat_number = p.pat_number
 order by pda.despesa_acumulada

The difference that I see, is that in the first case, you only want one patient per city , the one that has spent more. But in the second case, you want all patients ordered by expense. It's not the same.