0

It's highly likely this has been answered elsewhere, I can't find anything so if you can link to another post that would be ace.

I have a MySQL table which lists tickets and payments, amongst other columns. I didn't design table and I can't change too much; adding a column is out of the question. Here's a simplified version of the table:

CREATE TABLE cases (
    id INT PRIMARY, 
    created TIMESTAMP, -- when created in db- each day the table is truncated and a new csv imported
    opened DATE, -- will differ from the timestamp, this is the column I want to group by
    total DECIMAL, -- not relevant in this question but i'm totaling this by month too
    tickets VARCHAR(256) -- the one I want to count, two tickets would look like '"foo1234","baa5678"', there is no pattern (i.e. Same prefix) in the tickets either
);

The tickets column references possibly multiple tickets. Each is wrapped in double quotes in the DB (I have a feeling this is a bit insecure, not my decision and I can't really change it), with each reference separated by a comma (if there is more than one).

There are other columns like client name and date closed, but they are irrelevant in this case

I've written a query which will group the rows by month, year and total the payments for each month. I want the results to also include the count of tickets, itemized monthly. It can be assumed that there will always be at least one ticket. On the front end I either count the commas and add one, or explode by comma and count the array which works fine for a hand full of rows (there are rarely more than 4 tickets in one row), but as this will form a report of the entire database I want it to be a bit more efficient.

I don't have any code to post (I'm not looking for someone to write it for me, just point me in the right direction, maybe there is a method i'm forgetting?), as I'm not really sure where to start with it, all other posts similar to this are about grouping and totaling numbers (which I also need to do but i'm ok with that part), but nothing on how to do it with a string. SUM(), but with a string.

outis
  • 75,655
  • 22
  • 151
  • 221
LoonyToadQuack
  • 3
  • 3
  • 1
    Pls show us an example of your tables (doesn't need to be true data) but we need at least the structure. – Daniel E. Apr 06 '16 at 09:52

1 Answers1

1

This is easily solved by using CONCAT_WS, read more about it here

CONCAT_WS will concatenate the strings with a seperator chosen by you, in your case you would do that with a comma: CONCAT_WS(',',string_column)

This wil give you a all the strings separated by commas. if you really need the count than you can continue with this: How to count items in comma separated list MySQL

Using this would give:

LENGTH(CONCAT_WS(',',string_column)) - LENGTH(REPLACE(CONCAT_WS(',',string_column), ',', ''))+1

+1 because you need have one more result than you have comma's

Community
  • 1
  • 1
Jester
  • 1,408
  • 1
  • 9
  • 21