0

I will try to explain this clearly.

I have a table of 200,000 records that stores pixel sizes for our product images. A variety of image sizes have been saved over 20 years, and products do not necessarily have images in all folders.

I am trying to find out how many products have all of their images over 800 pixels wide to inform design decisions for a new website.

The fields are:

  • ID (unique reference of product)
  • ImgNo (id of each image under product: 1 to 36)
  • CDN_W (width of image on CDN storage)
  • LG_W (width of image in LARGE folder)
  • LGR_W (width of image in LARGER folder)

Data looks like this:

  • ID 123
  • ImgNo 1
  • CDN_W 800
  • LG_W 400
  • LGR_W 1280
  • ID 123
  • ImgNo 2
  • CDN_W 600
  • LG_W 600
  • LGR_W 1280
  • ID ABC
  • ImgNo 1
  • CDN_W null
  • LG_W 800
  • LGR_W null

etc...

THE TASK

I need to query the data to find all products where ALL of the images in a particular folder are over 800 pixels wide. There are 3 folders, any of which could provide the best set of images over 800 pixels. I only want products where all images are over 800 - if there are 5 images and 1 is below 800 then that product should not be valid.

Ideally I'd like to identify the TOTAL number of products that are valid, and separately the actual list of products IDs.

Is there a way to do this in MS SQL?

Improve this question

1 Answer 1

Reset to default
1 
SELECT id
FROM table
GROUP BY id
HAVING MIN(CDN_W) > 800
   AND MIN(LG_W) > 800
   AND MIN(LGR_W) > 800

?

Improve this answer
2
  • Thanks. That seems to give me 170,000 results which is counting (I guess) the number of images, not the number of products. However, I may have used your example to get an answer - I have removed all but 1 HAVING and done 3 separate selects, 1 for each folder. I now have 8000 returned for the LGR_W, 1122 for CDN_W and 5 for LG_W. I'll go and check those results now. Thanks.
    – user2470281
    Commented Feb 28, 2020 at 12:23
  • @user2470281 SELECT id .. GROUP BY id cannot give more than 1 record per product - so 170k is the amount of products. If you need the count of such products you must use this query as a subquery, and count in outer query.
    – Akina
    Commented Feb 28, 2020 at 12:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.