CODEWITHSUNDEEP
mysqlsql
NorthBridge
NorthBridge

Reputation: 674

MySQL GROUP BY with custom values

Tried to search for something similar, but no luck so far.

I have a query executed upon a table containing phones logs (ID, number, source and phone name). Its purpose is to fetch the total number of calls grouped by different criterias on the phone name (there are like four or five different ones). Currently I'm doing something like this:

SELECT COUNT(phones_statistics.id) AS number_of_calls, phones_statistics.calldate 
FROM phones_statistics 
INNER JOIN phones ON phones_statistics.phone_id = phones.id 
WHERE phones.abbr NOT LIKE '@%' 
GROUP BY 
    YEAR(phones_statistics.calldate)
    + '-' 
    + MONTH(phones_statistics.calldate) 
    + '-' 
    + DAY(phones_statistics.calldate)
;

The problem, as you can probably already see, is that for each LIKE/NOT LIKE criteria, I have to build another query with the different criteria, and I guess this is going to become nasty soon (currently there are 5 queries, running for a total of 20 seconds before returning results).

So I wondered, isn't there some easy way to avoid multiple queries and do the trick by constructing a custom grouping like this:

SELECT 
  COUNT(phones_statistics.id) AS number_of_calls,
  phones_statistics.calldate,
  (something like a switch - 4 statements, 4 return values,
   assigned to this field)
  AS custom_field
...
rest of the query
...
GROUP BY custom_field,
    YEAR(phones_statistics.calldate)
    + '-' 
    + MONTH(phones_statistics.calldate) 
    + '-' 
    + DAY(phones_statistics.calldate)

Upvotes: 1

Views: 1838

Answers (2)

NorthBridge
NorthBridge

Reputation: 674

Just for the record, I've found another way to do this by using CASE...WHEN

SELECT 
   COUNT(phones_statistics.id) AS calls_number,
   DATE(phones_statistics.calldate), 
   CASE 
     WHEN phones.abbr LIKE '%pattern1%' THEN 'Result1'
     WHEN phones.abbr LIKE '%pattern2%' THEN 'Result2' 
     ELSE 'default_result' 
   END
 as type 
FROM phones_statistics 
INNER JOIN phones ON phones_statistics.phone_id = phones.id 
GROUP BY type, DATE(phones_statistics.calldate);

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271003

First, the date part of your group by clause does not make sense. I assume that you simply want date(calldate). However, you are producing the following value for today (2014-03-04):

2014 + '-' + 03 + '-' + 04

MySQL uses the '+' for adding numbers. It automatically converts strings to numbers based on leading numeric characters, with the value being 0 if there are none. In MySQL, the expression adds up to: 2021. To me, this seems like a strange thing to do.

I suspect you want something like this:

SELECT date(ps.calldate) as calldate,
       SUM(ps.abbr NOT LIKE 'patten1%') as numPattern1,
       SUM(ps.abbr NOT LIKE 'patten2%') as numPattern2,
       SUM(ps.abbr NOT LIKE 'patten3%') as numPattern3,
       SUM(ps.abbr NOT LIKE 'patten4%') as numPattern4,
       SUM(ps.abbr NOT LIKE 'patten5%') as numPattern5
FROM phones_statistics ps INNER JOIN
     phones p
     ON ps.phone_id = p.id 
WHERE ps.abbr NOT LIKE '@%' 
GROUP BY date(calldate);

In other words, use conditional aggregation and put each value in a separate column.

Upvotes: 1

Related Questions