Mysql – select columns with different conditions

MySQLquery

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

Best Answer

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

Related Solutions

Mysql – InnoDB deadlock with INSERT .. SELECT

the deadlock message sometimes will not show the full picture. It could be some other query is holding the lock which is not any of those two showed in the log. As you said, you have delete query in the beginning, that properly would be the case.

Here is a way I normally use to troubleshoot the deadlocks when you can not get all the info from the log.

mysql> set global log_output = 'table';
mysql> set global general_log=on;

Then you reproduce the problem, when the deadlock happens, turn off the general log. Find those two queries in the mysql.general_log, then use thread_id to find the start and end of the transaction. You will have all the queries in the transaction, then you can go through the logic, to find out which is the query or logic caused the problem.

Mysql – Indexing a GROUP BY Select with Multiple Inequalities

It seems there are a lot of rows which fit the conditions and database server needs to sort them for grouping. It probably even needs to create a temp table on disk if your data doesn't fit into memory. In-memory temporary table size is limited by max_heap_table_size and tmp_table_size variables, but what is more important - MEMORY tables use a fixed-length row-storage format. Variable-length types such as VARCHAR are stored using a fixed length. It means every varchar(20) value will consume 20 bytes of RAM. Add 3 bytes for dates and 4 for doubles and your row size will be 31 bytes. If 5% of 100 million rows fit the conditions than you need 150 MB temporary table to group your data. You can start with increasing the max temporary table size and check if it helps.

But also you can try to use another approach:

SELECT Symbol
    ,(SELECT Date FROM price_daily
        WHERE Symbol = s.Symbol
            AND Date BETWEEN '2002-07-31'AND '2017-07-31' 
            AND MarketCap >= 1000
            AND PriceToSales BETWEEN 2 AND 5
        ORDER BY Date LIMIT 1) AS min_date
FROM symbols AS s;

I assumed you have a table with list of symbols (Symbol data type should be the same).

Best Answer

Related Solutions

Mysql – InnoDB deadlock with INSERT .. SELECT

Mysql – Indexing a GROUP BY Select with Multiple Inequalities

Related Question