How to loop through a query

[shmideo]

Hi

Could do with some help. I have 20 clients (client-1, client-2, client-3) and so on. Each client makes several calls per day.
How can I get a row count from two columns (Date, clid) and echo the count for each client?

[Barand]

Total count for each client

SELECT clid, COUNT(*) as tot FROM tablename GROUP BY clid

Total count each day for each client

SELECT clid, date, COUNT(*) as tot FROM tablename GROUP BY clid, date

[shmideo]

Thanks Barand, This works, but due to the clid format, which for example: "Client-1-+012345678" <012345678>" . There may be several instances of the same number. It groups by clid and it does exactly as it's supposed to, but I'm trying to get a count for all rows per client, so is there a way to group by clid except say maybe only by the client name, ie, ignore everything after the '-+'?

[Barand]

There is if you design your tables correctly and don't just throw different bits of data together into a single field.

 

As a workaround for now you could try

... GROUP BY SUBSTRING_INDEX(clid, '+', 1)

[shmideo]

Yes exactly, however I have no control of the table layout but I'll give this a try. Thanks for your help.