Solved

Group function doesnt works

Posted on 2014-01-03
7
300 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 500 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
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
The purpose of this article is to demonstrate how we can use conditional statements using Python.
This video teaches viewers about errors in exception handling.
The goal of the video will be to teach the user the concept of local variables and scope. An example of a locally defined variable will be given as well as an explanation of what scope is in C++. The local variable and concept of scope will be relat…

696 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