# i need answer for this problem [closed]

I need to get How many days each customer place order?

Table name - `T_Order`

``````OrderId    OrderDate    MenuItemId  Quantity    CustomerId
1       2021-01-02    1            2            1
2       2021-01-02    3            1            1
3       2021-01-02    5            1            1
4       2021-01-15    1            3            3
5       2021-01-15    3            2            3
6       2021-01-20    2            1            4
7       2021-01-26    4            5            5
8       2021-01-26    5            5            5
9       2021-02-02    1            2            1
10      2021-02-02    3            2            1
11      2021-02-15    2            1            3
12      2021-02-15    3            1            3
13      2021-02-25    2            1            4
14      2021-02-25    3            1            4
15      2021-02-28    1            2            5
16      2021-03-06    2            1            1
17      2021-03-06    3            1            1
18      2021-03-10    4            3            3
19      2021-03-10    5            3            3
20      2021-03-18    2            1            1
21      2021-03-21    4            1            3
22      2021-03-21    5            1            3
``````

``````MenuId  Items             Price
1       Pizza             2500.00
2       Pasta             1000.00
4       Burgers           900.00
5       Cheesecake        500.00
``````

Table name - T-Customer

``````CustomerId  FirstName
1             Steve
3             Jon
4             Lisa
5             Robert

Note: If the total bill is more than Rs.3000 they offer a 5% discount for each customer. Further if the order placed by a loyalty card holder 10% discount will be given for the total bill.
``````

Also I need answers for the following questions.

``````1. what is the most purchased item for each customer?
2. When was the first order date for each customer?
3. Find jim's average income in March?
4. John wants to see a report on every price change for menu items. How you can fulfill this requirement?
``````
• Please only ask a single question at a time. If you need to, create separate questions, which references to this question if you need to provide context. Oct 20 at 13:51

The reason why you can see incorect result is that `COUNT(o.OrderDate)` returns number of rows where `o.OrderDate` is not null. While you are looking for distinct values. You may use code as per below, but I think that there should be an easier way (with one of the window functions)

``````with CTE AS
(
SELECT Distinct c.FirstName as FirstName,
o.OrderDate as date,
c.CustomerId
FROM T_Orders o
INNER JOIN T_Customer c
ON c.CustomerId = o.CustomerId
)
select max(FirstName), count(OrderDate)
from CTE
GROUP BY c.CustomerId
``````

The issue you're running into is you have the same `CustomerId` ordering on the same `OrderDate` multiple times, so the `COUNT()` function will be inclusive of dupe dates.

You can simply use the `DISTINCT` keyword inside the aggregate `COUNT()` method to minimally change your existing query like so:

``````SELECT MAX(c.FirstName) as FirstName, COUNT(DISTINCT o.OrderDate) as Days
FROM T_Orders o
INNER JOIN T_Customer c
ON c.CustomerId = o.CustomerId
GROUP BY c.CustomerId
``````

Also, you don't have to use an aggregate function on the `FirstName` field since it'll always be the same value within your existing `CustomerId` grouping (i.e. each `CustomerId` will always have 1 distinct `FirstName`). Rather you can add it to your `GROUP BY` clause, and it should not change the output of your results, like so:

``````SELECT c.FirstName, COUNT(DISTINCT o.OrderDate) as Days
FROM T_Orders o
INNER JOIN T_Customer c
ON c.CustomerId = o.CustomerId
GROUP BY c.CustomerId, c.FirstName
``````