CODEWITHSUNDEEP
mysql
jumpOnCommand
jumpOnCommand

Reputation: 107

MYSQL count multiple orders without grouping

I'm trying to produce a query that shows every order AND adds an additional column which shows if the customer has previously placed an order by NAME, ADDRESS or USER_ID, without grouping results.

I've tried:

SELECT *, COUNT(`NAME`) c FROM `salesorders` GROUP BY `NAME` HAVING c > 0

This shows all customer orders, however if the customer has previously ordered before, the customers name only shows once due to the grouping.

e.g. 

Name,        Date,       Previous_Orders
John Smith,  2019-11-28, 2
Lisa Thomas, 2019-11-20, 1

I would like it show both occurrences:

John Smith,  2019-11-28, 2
Lisa Thomas, 2019-11-20, 1
John Smith,  2019-05-07, 2

salesorders columns;

Name
User_ID
Date

Lets say the new column is Previous_Orders

Upvotes: 0

Views: 94

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133360

You could use a join with the subquery for count group by name 

SELECT s.*,  t.c 
FROM `salesorders`  s
INNER JOIN 
    (SELECT name, COUNT(*) c
     FROM salesorders
     GROUP BY name) t ON t.name  = s.name 
WHERE t.c > 0

Upvotes: 0

forpas
forpas

Reputation: 164064

For MariaDB 10.4.6 you can use COUNT() window function:

select *, 
  count(full_add) over (partition by full_add) Previous_Orders
from salesorders;

If the column Name is unique, then you can replace user_id with Name if you prefer.
If by Previous_Orders you mean the number of orders up to the date in the current row, then change to:

select *, 
  count(full_add) over (partition by full_add order by date) Previous_Orders
from salesorders;

See the demo.

Upvotes: 1

Related Questions