0

This question focus to the COUNT result

1st query will show the COUNT with date condition.

2nd query will show the COUNT without date condition.

Now I need a query which make the COUNT result in 1st query without date condition which is : COUNT(c.CustomerID) > ONLY with condition b.BookingCustomerID=c.CustomerID (without date condition)

c.CustomerTelephone, b.* > with condition WHERE b.BookingCustomerID=c.CustomerID AND BookingDate LIKE '%2019-05-04%'

Thanks.

SELECT COUNT(c.CustomerID), c.CustomerTelephone, b.*
FROM Booking b, Customer c 
WHERE b.BookingCustomerID=c.CustomerID
AND BookingDate LIKE '%2019-05-04%' 
GROUP BY c.CustomerID


SELECT COUNT(c.CustomerID), c.CustomerTelephone, b.*
FROM Booking b, Customer c  
WHERE b.BookingCustomerID = c.CustomerID
GROUP BY c.CustomerID
1

When doing a GROUP BY, it is improper to list anything other than the group-by columns and aggregates in the SELECT part. In your case, the values of b.* could be random.

Are you trying to compute both counts at the same time? If BookingDate is inc, then it is easy. If it is in the other table, then it gets messier. _WhenJOINing`, please make it clear which columns come from which tables._

SELECT COUNT(c.CustomerID) AS count_all,
       SUM(( SELECT BookingDate FROM Booking
                     WHERE BookingCustomerID = c.CustomerID
                       AND BookingDate LIKE '2019-05-04%' )) AS count_day
    FROM Customer AS c
    GROUP BY c.CustomerId

To get the rest them,

SELECT x.count_all, x.count_day
       c.CustomerTelephone,
       b.*
    FROM ( ... the query above ... ) AS x
    JOIN Customer AS c USING(CustomerID)
    JOIN Booking AS b ON b.BookingCustomerID = x.CustomerID
    FROM Customer AS c
    GROUP BY c.CustomerId
| improve this answer | |
-1

How its possible to "group by" a column with same with "count" . In your case you group by with customerId and also COUNT with customerId.

Can you please change group by Condition. on which basis(column) you want to COUNT by customerId.

| improve this answer | |
  • you can try out ..following query.. select cnt,y.* from ( SELECT COUNT(c.CustomerID) cnt,BookingID FROM Booking b, Customer c WHERE b.BookingCustomerID=c.CustomerID GROUP BY BookingID ) as x inner join ( SELECT c.CustomerTelephone, b.* FROM Booking b, Customer c WHERE b.BookingCustomerID = c.CustomerID AND BookingDate LIKE '%2019-05-04%' ) as y on z.BookingID =y.BookingID – HiteshRathi May 10 '19 at 9:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.