StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSql Query To count "different way of stored data"
text
Body
copied!<p>Here is my Tables data</p> <p><strong>Tickets</strong></p> <pre><code>+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ + id | tic_files | +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ + 1 | 56456sasd.jpg,asd4455asd.jpg,asd564asd5.txt,asd564asd.css | +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ + 2 | 56a4sdasd.txt,jkasd5ass.jpg | +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ + 3 | | +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ + 4 | asdjhagsd.gif,spsnd65asd.php,56a4sd54asd.txt | +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ </code></pre> <p><strong>Comments</strong></p> <pre><code>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ + id | tic_id | com_files | ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ + 1 | 1 | 56456sasd.jpg,asd4455asd.jpg,asd564asd5.txt | ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ + 2 | 3 | 56a4sdasd.txt,jkasd5ass.jpg | ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ + 3 | 1 | sdf54sdf.gif,swrsdf54sdf.rar | ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ + 4 | 2 | asdjhagsd.gif,spsnd65asd.php,56a4sd54asd.txt | ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ </code></pre> <p>Here Is My Problem And My retries </p> <p>I want Result To Be Like This</p> <pre><code>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ + tic_id | count(tic_files) | count(com_files) | ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ + 1 | 4 | 5 | ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ + 2 | 2 | 3 | ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ + 3 | 0 | 2 | ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ + 4 | 3 | 0 | ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ </code></pre> <p><strong>i Tried To Do This</strong></p> <p>Join tickets with group_concat( comments ) and fetch data some thing like this</p> <pre><code>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ | tic_id | tic_files | com_files | ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ | 1 | 56456sasd.jpg,asd4455asd.jpg,asd564asd5.txt,asd564asd.css | 56456sasd.jpg,asd4455asd.jpg,asd564asd5.txt,sdf54sdf.gif,swrsdf54sdf.rar | ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ | 2 | 56a4sdasd.txt,jkasd5ass.jpg | asdjhagsd.gif,spsnd65asd.php,56a4sd54asd.txt | ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ | 3 | | 56a4sdasd.txt,jkasd5ass.jpg | ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ | 4 | asdjhagsd.gif,spsnd65asd.php,56a4sd54asd.txt | | ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ </code></pre> <p>But i will try to use more than group_concat ( in all servers limit is 1024 kb ) so i want to get count use delimiter between files " , "</p> <p><strong><em>Solved</em></strong></p> <p>This is my query</p> <pre><code>SELECT cat_id as cat__id , cat_name,( SELECT count(tickets.tic_id) from tickets INNER JOIN cats on(tickets.tic_cat = cats.cat_id) where tickets.tic_cat = cat__id group by tickets.tic_cat limit 1 ) as "count(tickets)",( SELECT ( LENGTH( GROUP_CONCAT(tickets.tic_files)) - LENGTH( REPLACE( GROUP_CONCAT(tickets.tic_files ) , "," , "" ) ) + 1 ) FROM tickets INNER join cats on ( tickets.tic_cat = cats.cat_id ) WHERE tickets.tic_files != "" AND tickets.tic_cat = cat__id LIMIT 1 ) as "count(ticket_files)",( SELECT GROUP_CONCAT(tickets.tic_files) FROM tickets inner join cats on ( tickets.tic_cat = cats.cat_id ) WHERE tickets.tic_files != "" AND tickets.tic_cat = cat__id LIMIT 1 ) as "tickets_files", COUNT(comments.tic_id),( SELECT ( LENGTH( GROUP_CONCAT(comments.com_files)) - LENGTH( REPLACE( GROUP_CONCAT(comments.com_files ) , "," , "" ) ) + 1 ) FROM comments INNER join tickets on ( tickets.tic_id = comments.tic_id ) INNER join cats on ( tickets.tic_cat = cats.cat_id ) WHERE comments.com_files != "" AND comments.tic_id = tickets.tic_id and tickets.tic_cat = cat__id LIMIT 1 ) as "count(com_files)",( SELECT GROUP_CONCAT(comments.com_files) FROM comments INNER join tickets on ( tickets.tic_id = comments.tic_id ) inner join cats on ( tickets.tic_cat = cats.cat_id ) WHERE comments.com_files != "" AND comments.tic_id = tickets.tic_id and tickets.tic_cat = cat__id LIMIT 1 ) as "com_files" from tickets INNER JOIN cats ON (tickets.tic_cat = cats.cat_id) INNER JOIN comments ON ( comments.tic_id = tickets.tic_id ) group by tickets.tic_cat </code></pre> <p>and here is picture of result</p> <p><img src="https://i.stack.imgur.com/5TDUm.jpg" alt="Result Of My query"></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload