PHP Versions Question posted in
You can find a list of all versions here.

Can't find the correct SQL function ( SUM/COUNT – LEFT JOIN ) – PHP Versions

My issue with SQL function, is that I often mix SUM / COUNT, or even where I can put a WHERE clause and a GROUP BY, short version, SQL is one of my flaws.

Ok, now here’s my current issue, I need to make a SQL function (in my PHP Model) that get the number of order I had "today" and their total cost, but I need to separate the result by country (sorry for my english) , for example the result I want should look like this (let’s say I had 20 orders today) :
U.S : 15 orders (for a total of 288$) , CANADA : 5 orders (for a total of 94$)

I tried many things, and the only two that didn"t give me an error were these two (but far from the result I need) :

SELECT sum(o.amount) AS totalmoney, p.name, p.id, p.name_clean, p.iso_code 
FROM orders AS o 
LEFT JOIN customers AS c ON o.id_customer = c.id 
LEFT JOIN countries AS p ON c.country = p.id    
WHERE DATE(o.date_create) = CURDATE()

SELECT sum(o.amount) AS totalmoney, o.date_create, p.name, COUNT(p.id) AS totalpays,
p.name_clean, p.iso_code, c.country 
FROM orders AS o 
LEFT JOIN customers AS c ON o.id_customer = c.id 
LEFT JOIN countries AS p ON c.country = p.id 
GROUP BY o.date_create

My three tables are (table name : column usefull) :

countries : id – name

customers : id – country_id

orders : id – id_customer – amount ($) – date_create

Can you please help me?

Thank you for your time.

————- EDIT WITH THE ANSWER I USED —————

Thanks to everyone who participate and helped me to have a better understanding of SQL.

SELECT sum(orders.amount) total, countries.name
FROM orders 
INNER JOIN customers ON orders.id_customer = customers.id
INNER JOIN countries ON countries.id = customers.country_id
WHERE orders.date_create = ?
GROUP BY customers.id

It’s exactly what I needed.

Tags:

4

Answers


  1. 0

    This should do what you want it to do:

    SELECT 
    c.name,
    sq.totalmoney
FROM
    countries as c
JOIN
    (SELECT 
        SUM(o.amount) AS totalmoney,
        c.id
    FROM
        (SELECT 
            *
        FROM 
            orders 
        WHERE 
            DATE(date_create) = CURDATE()
        ) AS o 
    JOIN 
        customers AS c 
    ON 
        o.id_customer = c.id
    GROUP BY c.id) AS sq
on c.id = sq.id;
    Login or Signup to reply.
  2. 0

    Try with that:
    — PostgreSQL

    drop table orders;
drop table countries;
drop table customers;

CREATE TABLE countries
(
    id   integer not null,
    name varchar(200)
);

insert into countries(id, name) values (1, 'MEXICO');
insert into countries(id, name) values (2, 'US');
insert into countries(id, name) values (3, 'CANADA');

create table customers
(
    id   integer not null,
    name varchar(200),
    country integer not null
);

insert into customers(id, name, country) values (1, 'Huey',1);
insert into customers(id, name, country) values (2, 'Dewey',2);
insert into customers(id, name, country) values (3, 'Louie',3);

create table orders
(
    id          integer not null,
    id_customer integer not null,
    amount      double precision,
    date_create date
);

insert into orders values (1, 1,  500.0, '20200103');
insert into orders values (2, 1,  1000, '20200103');
insert into orders values (3, 2, 500, '20200103');
insert into orders values (4, 3, 500, '20200103');
insert into orders values (5, 1,  500, '20200103');

SELECT COUNT(*) AS num_orders,  sum(o.amount) AS totalmoney, p.name
FROM orders AS o
         INNER JOIN customers AS c ON o.id_customer = c.id
         INNER JOIN countries AS p ON c.country = p.id
WHERE DATE(o.date_create) = '20200103'
 group by   p.name
order by num_orders desc;
    Login or Signup to reply.
  3. 0

    You’re wanting to group by country on a certain date, therefore your group by is country, and the where is the date:

    
SELECT sum(orders.amount) total, countries.name
FROM orders 
INNER JOIN customers ON orders.id_customer = customers.id
INNER JOIN countries ON countries.id = customers.country_id
WHERE orders.date_create = ?
GROUP BY customers.id

    Inner join is appropriate here, because the joins should only produce a single row for customer and country per order.

    Login or Signup to reply.
  4. 0

    you can use over whith sum and count

    select sum(amount) over(partition by contry_id) as totalamount,
       count(contry_id) over(partition by contry_id) as totalcount,
       name, ord.id 
from orders ord left join customers cus on ord.customer_id = cus.id
                left join countries con on cus.contry_id = con.id
where date_create = [date]  --using string as a pseudo date when testing, so change is needed

    here is a db<>fiddle with some pseudo data.

    this will also get the data group by created date which match the OP need more imo.

    select distinct sum(amount) over(partition by contry_id,date_create) as totalamount,
       count(contry_id) over(partition by contry_id,date_create) as totalcount,
       name, date_create
from orders ord left join customers cus on ord.customer_id = cus.id
                left join countries con on cus.contry_id = con.id

    pros with window function : over is that you can have different aggregate function with different ‘condition’ and don’t need to group by every non aggregate column which usually lead to a lot of subqueries.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search