Group mysql data

Asked

Viewed 78 times

0

Given two tables groups and processes. Where each group can have 0 or N processes. What is the best way to get this crossing.

Grupo
--------
id
Nome
bol_ativo = S

Processo
-------
id
numero
ativo  = S
encaminhado = N

what desire would be the amount of active and not forwarded processes of each active group. If the group has no process, it should appear as O in the total column

Resultado
----------
NOME      Total
g1          0

g2          3
mysql

1 answer

3


Assuming the following working DDL:

CREATE DATABASE `stackoverflow`;

CREATE TABLE `Grupo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `Nome` varchar(85) DEFAULT NULL,
  `bol_ativo` char(1) DEFAULT 'S',
  PRIMARY KEY (`id`)
);

CREATE TABLE `Processo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `numero` int(11) DEFAULT NULL,
  `ativo` char(1) DEFAULT 'S',
  `encaminhado` char(1) DEFAULT 'N',
  PRIMARY KEY (`id`)
);

And as a response to the following DML query:

INSERT INTO `Grupo` (`id`,`Nome`,`bol_ativo`) 
    VALUES  (1,'g1','S'),
            (2,'g2','S'),
            (3,'g3','S'),
            (4,'g4','N');


INSERT INTO `Processo` (`id`,`numero`,`ativo`,`encaminhado`)
    VALUES  (1,1,'S','N'),
            (2,1,'S','N'),
            (3,1,'S','N'),
            (4,2,'S','N'),
            (5,2,'S','N'),
            (6,1,'S','N'),
            (7,2,'S','N'),
            (8,2,'S','N'),
            (9,4,'S','S'),
            (10,2,'N','N'),
            (11,3,'N','N');
            
SELECT `g`.`Nome` AS `NOME`, COUNT(`p`.`id`) AS `Total`
FROM `Grupo` `g`
LEFT JOIN (
        SELECT * FROM `Processo` `p` 
        WHERE 1=1
            AND `p`.`ativo` = 'S'
            AND `p`.`encaminhado` = 'N'
    )  p ON (`p`.`numero` = `g`.`id`)
WHERE 
    `g`.`Bol_ativo` = 'S' 
GROUP BY `g`.`Nome`;

Reference:

[Paul Dubois at al, 2005], Mysql® 5.0: Certification Study Guide

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.