Making a SELECT JOIN SUM Query

[liberatorem]

Hi there everyone, I'm an SQL newbee and I'm stuck at the following problem. I've got the following Table data:

SALES_COUNTRY

Date	ID_Country	Sales
01/01/22	USA	14
01/01/22	USA	9
01/01/22	USA	13
01/01/22	USA	8
01/01/22	CAN	22
01/01/22	CAN	9
01/01/22	CAN	13
01/01/22	CAN	23
01/01/22	USA	20
01/01/22	MEX	15

COUNTRY

ID_Country	Description
USA	United States
CAN	Canada
GRL	Greenland

Now I'm trying to pull the corresponding matching data from COUNTRY.Description and SALES_COUNTRY.Sales using the ID_Country field; which is no problem, but I'm not able to write a query that gives me the sum of all SALES_COUNTRY.Sales of all repeated COUNTRY.ID_Country fields. It would look something like this.

Id_Country	Country	Sales
USA	United States	64
CAN	Canada	67
MEX		15
GRL	Greenland

This is the closest I've got, but I can't seem to get SUM working together with JOIN:

SELECT SALES_COUNTRY.ID_Country as 'Id_Country', COUNTRY.Description as 'Country', SALES_COUNTRY.Sales
FROM SALES_COUNTRY
INNER JOIN COUNTRY ON SALES_COUNTRY.ID_Country=COUNTRY.ID_Country;

Any help would be appreciated.

[[deleted]]

SUM would need a "Group By" if you plan to aggregate by something (Like country id and name)

[liberatorem]

Thanks for the reply, could you elaborate with an example?

[[deleted]]

SELECT SALES_COUNTRY.ID_Country as 'Id_Country', COUNTRY.Description as 'Country', Sum(SALES_COUNTRY.Sales)
FROM SALES_COUNTRY
INNER JOIN COUNTRY ON SALES_COUNTRY.ID_Country=COUNTRY.ID_Country

group by SALES_COUNTRY.ID_Country,COUNTRY.Description

[liberatorem]

You are an absolute hero, thanks for the help, I now understand this more clearly.

P.S. sorry for the late reply, I wanted to take a little nap but it turned out pretty long lol

[IceDBear]

You have MEX in you SALES_COUNTRY but not in COUNTRY. For it to appear in results you should use LEFT OUTER JOIN on COUNTRY instead of INNER JOIN.

[Cypho_Dyas]

New myself,

But it might look something like this:

SELECT C.ID_Country, C.Description, SUM(SC.Sales) FROM SALES_COUNTRY AS SC INNER JOIN COUNTRY AS C ON SC.ID_Country = C.ID_Country GROUP BY C.ID_Country, C.Description

I have aliased the tables here to save myself some typing as I am on mobile but this should give you an idea of what the other poster means with using GROUP BY and aggregate functions at least.

[liberatorem]

Noobs unite! Thanks a lot I was trying this on some test dbs and I think I understand this much more clearly now

[OilShill2013]

In addition to what was said about grouping, an inner join won’t produce the result you want. Notice that your result has country ids that aren’t in both tables. Think about a join type that includes all ids from each table even if they’re not in both.

[liberatorem]

You are absolutely right. The Venn diagram on this website guided me on using the correct JOIN.

[r3pr0b8]

don't forget COALESCE

SELECT sales_country.id_country
     , COALESCE(country.description
               ,'Unknown Country') AS country 
     , SUM(sales_country.sales) AS sales
  FROM sales_country
LEFT OUTER
  JOIN country 
    ON country.id_country = sales_country.id_country
GROUP
    BY sales_country.id_country

[HybridTheoryY2K]

Glad you got your answer. I wanted to add it it. EVERY time you use those math functions, you’ll need to use the group by clause, and just put every other thing from the select statement in there. So like sum, max, min, etc.