0

Exemple:

I have a HUGE QUery with union that brings a lot of data, among this data, I want to know, how many times records of the same person exists only the field name, Like:

Gabriel
- Record 1
- Record 2
- Record 3

João
- Record 1
- Record 2
- Record 3

So Gabriel and João each one has 3 data rows in my RecordSet, I need to have another field that counts that many of times it repeats, but also I need all the rows at same time, that's why I Can NOT use groupby.

Update MySql View

PlayHardGoPro
  • 2,791
  • 10
  • 51
  • 90
  • 4
    Your query is not readable and it discourages to help you. Make an efort and remove some junk from the query. – Pellared Oct 15 '13 at 20:41
  • Everytime I try to remove some junk and leave only the essencial I get negative... I'll Try – PlayHardGoPro Oct 15 '13 at 20:42
  • 1
    Look at the SQL answer to this question: http://stackoverflow.com/questions/19385738/distinguish-duplicates-in-a-foreach-loop-from-sql/19385994 In your case, if you make the above query into a VIEW, then it makes it easier to use the approach in the provided link to do an INNER JOIN on a (SELECT GROUP BY) from the same VIEW and get your count totals. I agree with other commenter that this query looks awful, so you may hit a performance issue with the approach I am suggesting, but it will get you what you asked for. – David Fleeman Oct 15 '13 at 20:48
  • what this query ? fields , somefields, someclause , some ... some what ? – echo_Me Oct 15 '13 at 20:52
  • @echo_Me I had the full code then I removed because someone told me it was TOO MUCH and would scary the people... SomeFields is just the Name of some fields... I removed it so the query would looks cleaner... Want the full code? – PlayHardGoPro Oct 15 '13 at 20:53
  • Want the full Code? I can edit with the full code again @DavidF – PlayHardGoPro Oct 15 '13 at 20:55
  • @PlayHardGoPro - no need to provide full code – David Fleeman Oct 15 '13 at 20:57
  • if you wanna provide i prefer that you provide a [sqlfiddle](http://sqlfiddle.com/) example. – echo_Me Oct 15 '13 at 20:58
  • @Strawberry Ok, and what does your comment help with the solution ? This query has been made a long time ago... I did not create it, but I'm in charge to provide support on this. – PlayHardGoPro Oct 16 '13 at 11:41
  • @DavidF I created the view but still don't know what to do. I'm sorry for bothering you but I really need to get this value to accomplish my task and It's the only way to do so =\\ Anyother help ? – PlayHardGoPro Oct 16 '13 at 12:10
  • 1
    @PlayHardGoPro - please post what your query against your view looks like without the count. – David Fleeman Oct 16 '13 at 13:33
  • @DavidF Updated my question. It's huge, hope it does not scares you =x and thank you ! – PlayHardGoPro Oct 16 '13 at 13:42
  • 1
    @PlayHardGoPro - I will be putting my query in as an Answer now that you have a working view. I also assume that you are counting on the field "Nome" instead of "name" as you stated in the post. – David Fleeman Oct 16 '13 at 14:40

1 Answers1

1

Now that you have a view wrapping your complicated logic, you could structure a query like this to get what you are looking for:

SELECT vt.*, vtij.nome_count
FROM view_teste vt
    INNER JOIN (SELECT Nome, count(1) as nome_count FROM view_teste
        WHERE [additional_filter_conditions]
            GROUP BY Nome) vtij on vtij.Nome = vt.Nome
WHERE [additional_filter_conditions]

Additional notes: (1) This may not be performant, but worth trying since you did not originate this query! Optimizing this query would be a beast. (2) You have an ORDER BY clause in your view which is a performance hit that is unnecessary. You should remove that and do the desired ORDER BY in your query, not inside your view.

David Fleeman
  • 2,588
  • 14
  • 17
  • I know that this query is all messed up , but as I said, it was made a long time ago and not by me. I'm a little confused, where exactly should I Run this ? I mean, I never used view before... ;X I know you don't have to answer such a silly question, but if you could give me a favor hehe =x Thanks ! – PlayHardGoPro Oct 16 '13 at 14:51
  • 1
    @PlayHardGoPro - once you have a VIEW in the database, you treat it just like a table when you write a query. http://stackoverflow.com/questions/6015175/difference-between-view-and-table-in-sql – David Fleeman Oct 16 '13 at 15:01
  • thank you ! I'm just confused about how/Where I'm going to insert that piece of query in the middle of the existing one... – PlayHardGoPro Oct 16 '13 at 17:12
  • @PlayHardGoPro -- the existing query is now "buried" in the view in the database. I am proposing REPLACING the ugly UNION query with the much simpler looking query in your application code. The ugly UNION query executes inside the database when you reference the view, no need to have it in your application layer. – David Fleeman Oct 16 '13 at 17:42