I want to count the records in column 1 that start with 123, I need to count multiple times, with different values (123, 657, 897, ...) I have the values in column 2
- column1 | column2
- 1234567 | 123
- 5489186 | 135
- 1238756 | 548
SELECT column1, LEFT(column1,3) AS prefijo, column2 COUNT(LEFT(column1 = 123))
FROM DDBB.table
Suppose your table has the data
column1 | column2
------------------
'123123' | '123'
'123456' | '231'
'123321' | '312'
'123654' | '321'
'456123' | '213'
'456654' | '132'
'456456' | '456'
'789123' | '564'
'789321' | '645'
If you want to know how many values in column2 share the first 3 characters in column 1:
select left(column1,3) prefijo,
count(*) cantidad
from mitabla
group by left(column1,3);
This delivery:
prefijo | cantidad
------------------
123 | 4
456 | 3
789 | 2
This should work for you.
SELECT provincia, column1, LEFT(column1,3) AS prefijo, column2 COUNT(LEFT(column1 = 123))
FROM DDBB.table
Where prefijo between 000 and 1000
On the other hand, what you need are specific values.
SELECT column1, LEFT(column1,3) AS prefijo, column2 COUNT(LEFT(column1 = 123))
FROM DDBB.table
Where prefijo in('123','657','897')
If you want to group by provinces you will need something type:
SELECT column1, LEFT(column1,3) AS prefijo, column2 COUNT(LEFT(column1 = 123))
FROM DDBB.table
Where prefijo in('123','657','897')
Group by provincia
Obviously, you have to specify the correct tables.
Based on the comments above, I think what you need is something like this - > SqlFiddle
Taking into account the following table design:
llamadas | prefijo | ciudad
---------+---------+-------
123456789| 123 | AAAA1
987546312| 987 | AAAA1
963852741| 963 | AAAA3
789456123| 789 | BBBBB
The query to be made would be:
select COUNT(llamadas) Llamadas, ciudad Ciudad
from ddbb
group by ciudad
And the resulting data would be these:
Llamadas Ciudad
2 AAAA1
1 AAAA3
1 BBBBB