How to unite two SQL functions?-CodePudding

I have 2 bits of SQL code written which I need to unite. However I can not come with a right solution.

Query #1:

SELECT name 
FROM payment_method;

Query #2:

SELECT COUNT(*) 
FROM orders 
GROUP BY payment_method_id;

The final code should show total number for each payment (how many times each payment has been used).

My code does show table where all names are listed but instead of counting payments with same id it counts all payment_methods that are shown in second table.

What's the exact problem?

My final code:

SELECT name, COUNT(*) AS orders
FROM payment_method
JOIN orders ON payment_method_id  
GROUP BY name;

payment_method table contains id and name

orders table contains id, payment_method_id and payment_completed_date

CodePudding user response：

When you are joining tables you need to define the relation how the tables should be joined. also, it is good practice to alias the tables in case they share names of some columns.

SELECT 
    p.name, COUNT(o.id) AS orders_count
FROM 
    payment_method p
JOIN 
    orders o ON o.payment_method_id = p.id
GROUP BY 
    o.payment_method_id;