CODEWITHSUNDEEP
sqloracle-database
user2501165
user2501165

Reputation: 191

SQL Return At least Data

I have SQL data in the following structure:

Car_Color  | Model_tag 
---------------------- 
  Green    | Fairytail1
  Red      | Tail2
  Blue     | Fairy3
  Green    | Hi3
  Green    | Test45
  Green    | Testing34

I would like to get the output as below that return the following query: Find all names of car_color that have at least four associated model_tag, and for these colors, the number of model_tags written in each car_color. 

Car_Color  | number_of_tags
----------------------------
  Green    |  4

Here is my query:

SELECT car, model_tag
FROM Model

Problem is that this following query doesn't return any data.

Upvotes: 0

Views: 40

Answers (3)

eifla001
eifla001

Reputation: 1157

try this also, I actually just copied answer above and put DISTINCT

SELECT c.car_color, COUNT(DISTINCT mt.tag_name) as numer_of_tags 
  FROM Model m JOIN
      model_tag mt
     ON mt.tag_id = m.model_id JOIN
      car c
      ON c.car_color_id = m.model_id
 GROUP BY c.car_color
HAVING COUNT(DISTINCT mt.tag_name) >= 4;

Upvotes: 0

sanatsathyan
sanatsathyan

Reputation: 1763

I dont think you can get the desired output if you group model_tag.tag_name, try this : 

SELECT car.car_color, COUNT (model_tag.tag_name) as numer_of_tags 
FROM Model
JOIN model_tag ON model_tag.tag_id = model.model_id
JOIN car ON car.car_color_id = model.model_id
GROUP BY car.car_color
HAVING COUNT(car.car_color_id)>=4

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Fix the aggregation logic:

SELECT c.car_color, COUNT(mt.tag_name) as numer_of_tags 
FROM Model m JOIN
     model_tag mt
     ON mt.tag_id = m.model_id JOIN
     car c
     ON c.car_color_id = m.model_id
GROUP BY c.car_color
HAVING COUNT(mt.tag_name) >= 4;

Upvotes: 4

Related Questions