After some research I haven't found what I need and I thought I'll ask here. I'm currently trying to develop a advanced search mode for a application and I'm stuck with my task. Maybe you can help me. So imagine I have the following table:
ID | Name | Surname
1 | John | Mim
2 | Johnny | Crazy
3 | Mike | Something
4 | Milk | Milk
5 | Peter | IDontknow
6 | Mitch | SomeName
Then in my frontend, there's one input field. The input of that field will go trough the query in that way:
SELECT name, surname FROM people WHERE name LIKE 'input%' OR surname LIKE 'input%'
Now lets say my input is "Mi", so I'll have 3 columns match in the "name" column, and 2 in the surname. And that's what I'm looking for.
A count which ouputs the following:
Column | Count
Name | 3
Surname | 2
Is there a way to achieve this in only one query?
What I've tried so far:
I actually created the table above on my localhost in my database and tried different queries. Tried with SELECT count(name), count(surname), but that would output 3 for both counts. So I'm not even sure if that's possible in only one query.
use union all
SELECT 'name' as col, count(name) as cnt FROM people WHERE name LIKE 'input%'
union all
SELECT 'surname', count(surname) FROM people WHERE surname LIKE 'input%'