select columns with different conditions

Question

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

Answer 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

Answer 2

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.