Problems counting in a Query

Question

Problems counting in a Query

Navigation

#1 by (4 votes)

0

The structure of the tables is as follows:

child

CREATE TABLE CHILD(
child_id SMALLINT,
child_name VARCHAR(255) NOT NULL,
birth_date DATE NOT NULL,
gender VARCHAR(255) NOT NULL,
address VARCHAR(255),
city VARCHAR(255),
CONSTRAINT PK_CHILD PRIMARY KEY(child_id)

letter

CREATE TABLE LETTER(
letter_id SMALLINT,
arrival_date DATE NOT NULL,
number_toys INTEGER NOT NULL,
delivery_mode VARCHAR(255) NOT NULL,
child_id SMALLINT,
CONSTRAINT PK_LETTER PRIMARY KEY(letter_id),
CONSTRAINT CHILD_FK FOREIGN KEY (child_id) REFERENCES CHILD(child_id)
);

wished_toy

 CREATE TABLE WISHED_TOY(
 letter_id SMALLINT,
 toy_id SMALLINT,
 CONSTRAINT PK_WISHED_TOY PRIMARY KEY(letter_id, toy_id),
 CONSTRAINT LETTER_FK FOREIGN KEY (letter_id) REFERENCES LETTER(letter_id)
 CONSTRAINT LETTER_FK FOREIGN KEY (toy_id) REFERENCES TOY(toy_id)
 );

toy

CREATE TABLE TOY(
toy_id SMALLINT,
toy_name VARCHAR (255) NOT NULL,
price DECIMAL NOT NULL,
toy_type VARCHAR(255) NOT NULL,
manufacturer VARCHAR(255) NOT NULL,
CONSTRAINT PK_TOY PRIMARY KEY(toy_id),
);

I was asked for a query with the toys ordered (wished_toy) at least 3 times in letters received from the year 2016.

I have this, which shows me every letter (letter) requested since 2016 every game that has been requested, which is not like telling the toy_id that are the same to filter the toys that have been requested at least 3 times.

select l.letter_id,wi.toy_id 
from letter l
join wished_toy wi
on l.letter_id=wi.letter_id
where l.arrival_date >= '2016-01-01'
group by l.letter_id,wi.toy_id
order by letter_id asc

Get this out:

   letter_id   toy_id
       1          3
       1          4
       1          1
       2          4

And so ... the case is how I can add or count the toys that have been requested repeated at least 3 times.

sql postgresql postgresql-9.3

asked by Roman345 31.10.2017 в 09:42

source

1 answer

Random_shuffle: Show the shuffled items on the screen why does not it return all the rows in the gridView?

score 4 · Accepted Answer

The wished_toy table will not give you the name of the toy, which seems to be what they ask for. You have to joke with toy . I would do it like:

SELECT 
   toy.toy_name,
   COUNT(*) cantidad
FROM toy
JOIN wished_toy USING (toy_id)
JOIN letter l USING (letter_id)
WHERE l.arrival_date>='2016-01-01'
GROUP BY toy_name

This is based on the fact that the step table wished_toy has no duplicate records. That is, the same card can not ask for the same toy twice. (Santa Claus, please bring me a doll, a monopoly and a doll, WTF). Maybe it would be more defensive to do

SELECT 
   toy.toy_name,
   COUNT(DISTINCT letter_id) cantidad
FROM toy
JOIN wished_toy USING (toy_id)
JOIN letter l USING (letter_id)
WHERE l.arrival_date>='2016-01-01'
GROUP BY toy_name

But in the end, if the same letter asks twice for the same toy, I suppose it can be correct to count the toy twice and in that case the first query would be the one indicated.