Solved

Group function doesnt works

Posted on 2014-01-03
7
303 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
The goal of the video will be to teach the user the difference and consequence of passing data by value vs passing data by reference in C++. An example of passing data by value as well as an example of passing data by reference will be be given. Bot…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

688 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