1

On my quest to learn basic SQL I got stuck on this task: I need to find and count every instance where product prices differ by at most one (Including the product you are comparing)

From this example:

PRODUCTS
+-----------+------------+
|   name    |    price   |
+-----------+------------+
|paper      |      7     |
+-----------+------------+
|rock       |      4     |
+-----------+------------+
|scissors   |      8     |
+-----------+------------+
|gun        |      6     |
+-----------+------------+

The out come should be:

   name      How_many_times_price_differed
+-----------+------------+
|paper      |      3     |
+-----------+------------+
|rock       |      1     |
+-----------+------------+
|scissors   |      2     |
+-----------+------------+
|gun        |      2     |
+-----------+------------+

I have been doing these practises for 8 hours straight so my tilted brain came up with this stupid answer and its not working

SELECT name, COUNT(price <= price+1 OR price >= price+1)
FROM Products

Obviously this didn't work. No need to tell me exactly how its done but any idea where I should be doing the COUNT(), My newbie logic is running bit thin.

GMB
  • 216,147
  • 25
  • 84
  • 135
HuManatee
  • 67
  • 5

1 Answers1

2

One option uses a subquery:

select p.name,
    (
        select count(*) 
        from products p1 
        where p1.price between p.price - 1 and p.price + 1
    ) res
from products p

If your database supports window functions and range frames, this can be more efficiently done with a window count:

select name, 
        count(*) over(order by price range between 1 preceding and 1 following) res
from products

Demo on DB Fiddle - both queries yield:

name     | res
:------- | --:
gun      |   2
paper    |   3
rock     |   1
scissors |   2
GMB
  • 216,147
  • 25
  • 84
  • 135
  • Seems to work allright! Subquerys are bit new to me and they haven't teached anything about "between" yet :P. Seems like nice way to do it. What does that "res" do after the Subquery? – HuManatee Oct 16 '20 at 22:44
  • 1
    @HuManatee: `res` is just an alias for the count column in the resultset. I would also recommend looking at the window count solution: it is more efficient than the subquery. – GMB Oct 16 '20 at 22:49
  • Since you're learning SQL, I think these may be confusing (although correct). The first is an alternate of the following strategy. 1) Find the cost difference for each product pair e.g., `SELECT p1.Name, p2.Name, (p1.Price - p2.Price) PriceDiff FROM product P1 CROSS JOIN Product p2`. This generates a table with all the price differences. 2) Remove a lot of rows with a `WHERE` clause where PriceDiff is >= -1 and <= 1. These's now no need to call it a column - each row has the difference requirement. 3) Get the counts e.g., `GROUP BY p1.Name` and `SELECT p1.Name, count(*) AS res` – seanb Oct 17 '20 at 02:01