mysql - Very confused with SQL queries

Question

I just began learning SQL queries and things don't seem to work out as intended.

The problem given is as follows:

There are two tables in a database of real estate owners.

One has ownership information and the other has price information, in millions.

An owner may own multiple houses, but a house will have only one owner.

Write a query to print the IDs of the owners who have at least 100 million worth of houses and own more than 1 house.

The output order does not matter.

The result should be in the format: BUYER_ID TOTAL_WORTH.

The query that I wrote down is

SELECT house.BUYER_ID
     , SUM(price.PRICE) AS TOTAL_WORTH
  FROM house
  LEFT 
  JOIN price
    ON house.HOUSE_ID = price.HOUSE_ID
 GROUP 
    BY house.BUYER_ID
     , price.PRICE
 HAVING COUNT(house.HOUSE_ID) > 1

This didn't get the correct answer, as I could not add up the prices and the same BUYER_ID kept appearing multiple times in the result.

In this case, what do I need to fix in order to get the correct answer?..

question from:https://stackoverflow.com/questions/65830333/very-confused-with-sql-queries

Answer 1

What should you do?

Just remove price.PRICE from GROUP BY.

Why?

For testing your mysql I google for "mysql sandbox online" and use next schema for testing:

CREATE TABLE `house` ( `BUYER_ID` INT UNSIGNED NOT NULL , `HOUSE_ID` INT UNSIGNED NOT NULL) ENGINE = InnoDB;
CREATE TABLE `price` ( `HOUSE_ID` INT UNSIGNED NOT NULL AUTO_INCREMENT , `PRICE` INT UNSIGNED NOT NULL , UNIQUE (`HOUSE_ID`)) ENGINE = InnoDB;

INSERT INTO `house` (`BUYER_ID`, `HOUSE_ID`) VALUES ('1', '1'), ('1', '2'), ('1', '3'), ('2', '4'), ('3', '5');
INSERT INTO `price` (`PRICE`) VALUES ('70'), ('20'), ('30'), ('50'), ('10');

I use type integer for HOUSE_ID instead of string, just because it look more logical for me. Also, I use AUTO_INCREMENT for column HOUSE_ID in table price, so all prices automatically get their HOUSE_ID, e.g., 1 for price 70, 2 for price 20 etc.

As a result I get next two tables (house and price respectively) with data to work with: |BUYER_ID|HOUSE_ID| |HOUSE_ID|PRICE| |--------|--------|-|--------|-----| |1 |1 | |1 |70 | |1 |2 | |2 |20 | |1 |3 | |3 |30 | |2 |4 | |4 |50 | |3 |5 | |5 |10 |

When I try your query I get next result: |BUYER_ID|TOTAL_WORTH| |--------|-----------| |1 |20 | |1 |30 | |1 |70 | |2 |50 | |3 |10 |

As you can see it is just list of all existing rows joined from two tables.

Why did this happen?

You use GROUP BY for two columns

GROUP 
    BY house.BUYER_ID
     , price.PRICE

so mysql try to find all rows in table house that have same value in column BUYER_ID and group them (should be first three rows in my variant). But, at the same time, mysql also try to find all equal prices in table price. In my example not exist at least two rows with equal pair BUYER_ID - PRICE. As a result - no one row grouped.

To make it clearer, look on table below: |BUYER_ID|PRICE| |BUYER_ID*|PRICE*| |--------|-----|-|---------|------| |1 |20 | |1 |20 | |1 |30 | |1 |20 |

When we use your query, we will get next result: |BUYER_ID|TOTAL_WORTH| |BUYER_ID*|TOTAL_WORTH*| |--------|-----------|-|---------|------------| |1 |20 | |1 |40 | |1 |30 |

• In first case (columns without asterisk) we will get same two rows because prices are different
• In second case (columns with asterisk) we will get one row because both BUYER_ID* and PRICE* values equal

Small tip at the end

If you have columns with same names in different tables and need their values be equal you could use USING when join them

LEFT 
  JOIN price
  USING (HOUSE_ID)

It will equal to your query

LEFT 
  JOIN price
  ON house.HOUSE_ID = price.HOUSE_ID

If you have more columns that should be equal in join statement: put them to brackets and separate by comma:

USING (foo, bar)

Also, you could use aliases for tables same as you have use it for SUM in your query. It's allow write less code if you will have long table names:

FROM foo_is_my_table_name AS f

And use it like

SELECT f.bar_column

instead of

SELECT foo_is_my_table_name.bar_column

Hope it helps and happy learning ;)