[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Group function doesnt works

Posted on 2014-01-03
7
Medium Priority
?
305 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 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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 

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

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
The viewer will learn how to pass data into a function in C++. This is one step further in using functions. Instead of only printing text onto the console, the function will be able to perform calculations with argumentents given by the user.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

649 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