Group function doesnt works

I m trying to do a report with a count  of items that a user técnico has .




SELECT
     COUNT(*)Quantidade,
 item.`codigo` AS item_codigo,
 empresa.`codigo` AS empresa_codigo,
     tecnico_item.`codigo_item` AS tecnico_item_codigo_item,
     tecnico_item.`codigo_tecnico` AS tecnico_item_codigo_tecnico,
     usuario.`codigo` AS usuario_codigo,
     empresa.`nome_fantasia` AS empresa_nome_fantasia,
     item.`codigo_item` AS item_codigo_item,
     item.`criticidade` AS item_criticidade,
     item.`data_item` AS item_data_item,
     item.`descricao` AS item_descricao,
     usuario.`nome` AS usuario_nome,
     empresa.`data_fim` AS empresa_data_fim,
     empresa.`data_ini` AS empresa_data_ini


FROM
     `empresa` empresa INNER JOIN `item` item ON empresa.`codigo` = item.`codigo_empresa`
     INNER JOIN `tecnico_item` tecnico_item ON item.`codigo` = tecnico_item.`codigo_item`
     INNER JOIN `usuario` usuario ON tecnico_item.`codigo_tecnico` = usuario.`codigo`
     AND empresa.`codigo` = usuario.`codigo_empresa`

group by

 item.`codigo`,
 empresa.`codigo`,
     tecnico_item.`codigo_item` ,
     tecnico_item.`codigo_tecnico` ,
     usuario.`codigo` ,
     empresa.`nome_fantasia`,
     item.`codigo_item`,
     item.`criticidade` ,
     item.`data_item`,
     item.`descricao` ,
     usuario.`nome` ,
     empresa.`data_fim`,
     empresa.`data_ini`

Open in new window



The count isn't correct --> see in queryresult


When i pick only the manutomany table the count () its ok.

see bellow:

select 
   count(coditen) 
from tabela
where codtecnico = codtecnico
group by codtecnico

Open in new window

model.jpg
queruresult.jpg
Rafael_MoreiraAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin CrossChief Technology OfficerCommented:
I suspect you need to aggregate one table in a derived query, joining to the outer query.  Not sure I can make out exactly what you are after; however, here is a generic example to count records in tableB for a single record in tableA.

SELECT b.cnt, ...
FROM tableA a
-- use LEFT JOIN if you may not have a match to get zero counts
JOIN (
    SELECT col_matching_tableA, COUNT(*) cnt
    FROM tableB
    GROUP BY col_matching_tableA
) b ON b.col_matching_tableA = a.col_matching_tableB

;

I hope that helps!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rafael_MoreiraAuthor Commented:
I m trying this .

Take a look at image it seems that something between item and usuário lack.
query.png
tableItem.jpg
tableNxN.jpg
Kevin CrossChief Technology OfficerCommented:
What are you expecting?  One row with 27?  Just making sure I understand.
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Rafael_MoreiraAuthor Commented:
each group row per user very similar with the last result , but the items descriptions  are not complete .

The result is grouping some descriptions .


T0001      X      1969-12-31      VÁLVULA PARA MOTOR
V000011      X      2014-01-02      1FUSIVEL NH 00 80 A 500 V SIEMENS/3NA10202
V000015      X      2014-01-02      5FUSIVEL NH 00 80 A 500 V SIEMENS/3NA10206
V000019      X      2014-01-02      9FUSIVEL NH 00 80 A 500 V SIEMENS/3NA102010
V0000113      X      2014-01-02      13FUSIVEL NH 00 80 A 500 V SIEMENS/3NA102014
V0000117      X      2014-01-02      17FUSIVEL NH 00 80 A 500 V SIEMENS/3NA102018
V0000121      X      2014-01-02      21FUSIVEL NH 00 80 A 500 V SIEMENS/3NA102022
V0000125      X      2014-01-02      25FUSIVEL NH 00 80 A 500 V SIEMENS/3NA102026
V0000129      X      2014-01-02      29FUSIVEL NH 00 80 A 500 V SIEMENS/3NA102030
V0000133      X      2014-01-02      33FUSIVEL NH 00 80 A 500 V SIEMENS/3NA102034
V0000137      X      2014-01-02      37FUSIVEL NH 00 80 A 500 V SIEMENS/3NA102038
V0000141      X      2014-01-02      41FUSIVEL NH 00 80 A 500 V SIEMENS/3NA102042
V0000145      X      2014-01-02      45FUSIVEL NH 00 80 A 500 V SIEMENS/3NA102046
V0000149      X      2014-01-02      49FUSIVEL NH 00 80 A 500 V SIEMENS/3NA102050
V0000153      X      2014-01-02      53FUSIVEL NH 00 80 A 500 V SIEMENS/3NA102054
V0000157      X      2014-01-02      57FUSIVEL NH 00 80 A 500 V SIEMENS/3NA102058



i think that a join is lacking.
Rafael_MoreiraAuthor Commented:
I had to make many subqueryes to get what i want.

But your solution was very helpful
Kevin CrossChief Technology OfficerCommented:
I am glad you found what you needed and that I could help.

Best regards and happy coding,

Kevin
Rafael_MoreiraAuthor Commented:
you where wellcome.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.