SQL count using subquery

Question

I have a table users that lists all users who have entered orders in to the system.

Each order has a order_number and user amongst a ton of other columns.

Each week I am looking to get a list of the total orders that each user has entered in to the system, I guess this will need a subquery. I have looked at grouping and subquery but am really lost.

The idea is to SELECT a count of orders entered that week, entry_date between sysdate and sysdate-5 etc. which I don't have a problem doing, but I don't understand how to then count it per user.

So for e.g. Jane entered 150 orders, Steve entered 450 orders etc.

Can someone point me in the right direction here please?

You may want to post the schema for your various tables to help people formulate their answers. — Dmitriy Khaykin
– Dmitriy Khaykin, Commented Feb 2, 2014 at 21:01

Ross Attrill · Accepted Answer · 2014-02-02 21:01:30Z

4

I don't really think you need a sub-query. Won't a GROUP BY solve your problem?

SELECT USER, COUNT(*)
FROM ORDERS
WHERE ENTRY_DATE BETWEEN SYSDATE - 5 AND SYSDATE
GROUP BY USER

answered Feb 2, 2014 at 21:01

Ross Attrill

2,7321 gold badge28 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

badcc · Accepted Answer · 2014-02-02 21:06:14Z

1

Use GROUP BY. I made up some column names.

SELECT u.user, COUNT(*) FROM orders o, users u 
WHERE o.user = u.user AND o.entry_date BETWEEN o.sysdate - 5 AND o.sysdate 
GROUP BY u.user

answered Feb 2, 2014 at 21:06

badcc

2326 silver badges18 bronze badges

Collectives™ on Stack Overflow

SQL count using subquery

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related