?
Solved

Group function doesnt works

Posted on 2014-01-03
7
Medium Priority
?
307 Views
Last Modified: 2014-01-06
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
0
Comment
Question by:Rafael_Moreira
  • 4
  • 3
7 Comments
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 1500 total points
ID: 39754698
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!
0
 

Author Comment

by:Rafael_Moreira
ID: 39754981
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
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 39755008
What are you expecting?  One row with 27?  Just making sure I understand.
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

Author Comment

by:Rafael_Moreira
ID: 39755046
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.
0
 

Author Closing Comment

by:Rafael_Moreira
ID: 39760182
I had to make many subqueryes to get what i want.

But your solution was very helpful
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 39760221
I am glad you found what you needed and that I could help.

Best regards and happy coding,

Kevin
0
 

Author Comment

by:Rafael_Moreira
ID: 39760545
you where wellcome.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
The viewer will learn how to clear a vector as well as how to detect empty vectors in C++.
The viewer will be introduced to the member functions push_back and pop_back of the vector class. The video will teach the difference between the two as well as how to use each one along with its functionality.
Suggested Courses
Course of the Month15 days, 12 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question