How to select multiple rows in SQL if I have status attribute 1|2|3|4|5

Question

I know it's a basic one, but can anyone answer it? I'd really appreciate that.

I have one table called orders where I have to get the records of orders on the weekly basis, based on the order status which is 1|2|3|4|5 but the problem is same user can placed multiple orders with the same order value so I need the user Count with the OrderDate Constraint so that I can get the weekly user Count of order with their status.

I tried using a SQL command. The status of enum values which is considered to be as 1|2|3|4|5

NOTE: I can use Group BY of orderDate, I think that will give me a exact count of Users, but not sure how. db.Orders.GetAll().Where(q => (q.Status == OrderStatus.Complete || q.Status == OrderStatus.Processing || q.Status == OrderStatus.PartiallyShipped || q.Status == OrderStatus.Cancelled || q.Status == OrderStatus.Fraudulent || q.Status == OrderStatus.SuspectedFraud) && (startDate == null || DbFunctions.TruncateTime(q.OrderDate) >= DbFunctions.TruncateTime(startDate)) && (endDate == null || DbFunctions.TruncateTime(q.OrderDate) <= DbFunctions.TruncateTime(endDate)) ).Distinct().Count();

Does this answer your question? [How to do select from where x is equal to multiple values?](https://stackoverflow.com/questions/261783/how-to-do-select-from-where-x-is-equal-to-multiple-values) — Charlieface, Mar 16 '23 at 10:39
https://meta.stackoverflow.com/questions/388759/why-should-i-tag-my-rdbms — Reporter, Mar 16 '23 at 10:46
Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. — Community, Mar 16 '23 at 12:32
I want the weekly report of orders whose status either 1|2|3|4|5 but with the unique user email by adding all these condition I want the user count For Ex: Orders(Count), Users(Custoemrs) — shubham maini, Mar 16 '23 at 22:45

score 0 · Accepted Answer · answered Mar 16 '23 at 04:58

0

If I understand your question, here is a SQL query that gives a count of the number of orders in each of the status 1 through 5, within a time period:

 SELECT Status, COUNT(*) 
 FROM Orders 
 WHERE  OrderDate >= '2022-05-02' 
    AND OrderDate <= '2023-03-01'
    AND Status in (1,2,3,4,5)
 GROUP BY Status

answered Mar 16 '23 at 04:58

Phillip Ngan

15,482
8
63
79

thanks for the answer but you don't need to group by status If i have a orders in table and want to know how many orders is gets placed in a week and also needs to get the record by user Billing Email. then it is not work in that situation. Because One users can place multiple orders with same email and I need the count of every users it does not matter how many time he placed the order we need to group them all based on the weekly date constraint and order status as i mentioned and with the user email – shubham maini Mar 16 '23 at 22:50

How to select multiple rows in SQL if I have status attribute 1|2|3|4|5

1 Answers1