CODEWITHSUNDEEP
mysql
tuurbo
tuurbo

Reputation: 361

help with mysql joins/grouping

I'll try to make this simple.

This query lists my sites users, their total orders, cards, and addresses on file.

For some reason if the user has 2 addresses but only 1 order, it will show the customer has 2 orders.

For example: user4 actually only has 1 order, but shows 2 and im assuming its because he has 2 addresses and it has something to do with the joins or grouping.

SELECT
users.user_email,
users.user_firstname,
users.user_lastname,
users.user_joindate,
users.user_logindate,
Count(users_addresses.usera_id) AS count_addr,
Count(users_cards.userc_id) AS count_cards,
Count(orders.order_id) AS count_orders,
Sum(orders.order_total) AS sum_ordertotal
FROM
users
LEFT JOIN users_addresses ON users_addresses.usera_userid = users.user_id
LEFT JOIN users_cards ON users_cards.userc_userid = users.user_id
LEFT JOIN orders ON orders.order_userid = users.user_id
GROUP BY
users.user_id
ORDER BY user_id DESC
LIMIT 5

Example ouput:

userid | orders | addresses | cards
------ ----------------------------
user4  |   2    |     2     |   0
user3  |   0    |     0     |   0
user2  |   1    |     1     |   0
user1  |   0    |     1     |   0

Upvotes: 0

Views: 50

Answers (2)

user895553
user895553

Reputation: 1

As suggested before:

count(distinct(orders.order_id)) as count_orders

And you could use:

(select sum(order_total) from orders where user_id = users.user_id) as sum_ordertotal

Upvotes: 0

Doug Kress
Doug Kress

Reputation: 3537

One possibility is to use COUNT(DISTINCT orders.order_id). However, this doesn't help with the Sum().

Another possibility (albiet less efficient) is to use subqueries for the counts.

SELECT
    users.user_email,
    users.user_firstname,
    users.user_lastname,
    users.user_joindate,
    users.user_logindate,
    (SELECT count(*) FROM users_addresses WHERE users_addresses.usera_id = users.user_id) AS count_addr,
    (SELECT count(*) FROM user_cards WHERE users_cards.userc_id = users.user_id) AS count_cards,
    Count(orders.order_id) AS count_orders,
    Sum(orders.order_total) AS sum_ordertotal
FROM users
LEFT JOIN orders ON orders.order_userid = users.user_id
GROUP BY users.user_id
ORDER BY user_id DESC
LIMIT 5

Upvotes: 1

Related Questions