mySql - Make a select with condition on group by

user_2051816 0
Viewed 82

I have some data like these:

CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `idDoc` int(11) NOT NULL DEFAULT '0',
  `idProd` int(11) NOT NULL DEFAULT '0',
  `codProd` text NOT NULL,
  `qta` int(11) NOT NULL DEFAULT '0',
  `serNum` varchar(50) NOT NULL,
  `lotto` varchar(50) NOT NULL,
  `SNLTdett` varchar(50) NOT NULL DEFAULT '',
  `merce` varchar(11) CHARACTER SET utf8 NOT NULL DEFAULT 'PArticoli'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dump dei dati per la tabella `test`
--

INSERT INTO `test` (`id`, `idDoc`, `idProd`, `codProd`, `qta`, `serNum`, `lotto`, `SNLTdett`, `merce`) VALUES
(22856, 1262, 4069, 'COMPOSTO', 2, '', 'AAA', '', 'PArticoli'),
(22857, 1262, 4066, 'COMUNE', 4, '', '', '', 'PArticoli'),
(22858, 1262, 4061, 'FIGL1', 3, '', 'AAA', '', 'PArticoli'),
(22859, 1262, 4062, 'FIGL2', 3, '', 'BBB', '', 'PArticoli'),
(22860, 1262, 4068, 'LOTTO', 8, '', 'AAA', '', 'PArticoli'),
(22861, 1262, 4068, 'LOTTO', 4, '', 'BBB', '', 'PArticoli'),
(22862, 1262, 4067, 'SERIALE', 1, 'E', '', '', 'PArticoli'),
(22863, 1262, 4067, 'SERIALE', 1, 'C', '', '', 'PArticoli'),
(22864, 1262, 4067, 'SERIALE', 1, 'D', '', '', 'PArticoli'),
(22865, 1262, 4067, 'SERIALE', 1, 'A', '', '', 'PArticoli'),
(22866, 1262, 4067, 'SERIALE', 1, 'B', '', '', 'PArticoli'),
(22867, 1262, 4063, 'SNLT-1', 1, 'BBB', '', '', 'PArticoli'),
(22868, 1262, 4063, 'SNLT-1', 1, 'AAA', '', '', 'PArticoli'),
(22869, 1262, 4064, 'SNLT-1B', 1, 'RRR', '', '', 'PArticoli'),
(22870, 1262, 4064, 'SNLT-1B', 1, 'SSS', '', '', 'PArticoli'),
(22854, 1262, 4066, 'COMUNE', 2, '', '', '', 'PAccessori'),
(22855, 1262, 142, 'CAPSNLT', 2, '', '', '', 'anaVcap'),
(22853, 1262, 4067, 'SERIALE', 1, 'Q', '', '', 'PArticoli'),
(22852, 1262, 4067, 'SERIALE', 1, 'Z', '', '', 'PArticoli'),
(22873, 1262, 0, '', 1, '', '', '', 'varie');

I would like to have all the rows but the sum of qta only for the rows that have same idProd and merce and serNum !='' like these:

idDoc   idProd  codProd     qta   serNum          lotto     merce
1262    142     CAPSNLT     2                               anaVcap
1262    4069    COMPOSTO    2                     AAA       PArticoli
1262    4066    COMUNE      4                               PArticoli
1262    4066    COMUNE      2                               PAccessori
1262    4061    FIGL1       3                     AAA       PArticoli
1262    4062    FIGL2       3                     BBB       PArticoli
1262    4068    LOTTO       8                     AAA       PArticoli
1262    4068    LOTTO       4                     BBB       PArticoli
1262    4067    SERIALE     7     E,C,D,A,B,Q,Z             PArticoli
1262    4063    SNLT-1      2     BBB,AAA                   PArticoli
1262    4064    SNLT-1B     2     RRR,SSS                   PArticoli

I have made this query

SELECT idDoc, idProd, codProd, SUM(qta), lotto, SNLTdett,
GROUP_CONCAT(DISTINCT IF(serNum='','', serNum)) AS srnSch
FROM test 
WHERE idDoc=1262 AND (merce='PArticoli' OR merce='PAccessori' OR merce='anaVcap') 
GROUP BY idProd, merce

and it give me:

idDoc   idProd  codProd     qta   serNum          lotto     merce
1262    142     CAPSNLT     2                               anaVcap
1262    4069    COMPOSTO    2                     AAA       PArticoli
1262    4066    COMUNE      4                               PArticoli
1262    4066    COMUNE      2                               PAccessori
1262    4061    FIGL1       3                     AAA       PArticoli
1262    4062    FIGL2       3                     BBB       PArticoli
1262    4068    LOTTO       12                    AAA       PArticoli
1262    4067    SERIALE     7     E,C,D,A,B,Q,Z             PArticoli
1262    4063    SNLT-1      2     BBB,AAA                   PArticoli
1262    4064    SNLT-1B     2     RRR,SSS                   PArticoli

my problem is that I need:

22860   1262    4068    LOTTO       8               AAA     PArticoli
22861   1262    4068    LOTTO       4               BBB     PArticoli

My condition is : same (idProd and merce) and serNum !=''

How I can do it? How I can insert as condition serNum !='' in GROUP BY?

Here the fiddle http://sqlfiddle.com/#!9/4c2df/9

mysql sql
1 Answers

You can add an extra condition in the GROUP BY using the CASE expression. Here I assume that id is unique.

CASE WHEN serNum='' THEN id ELSE 0 END

this expression returns different (unique) IDs for the rows where serNum is empty, so all these rows will remain separate. All rows where serNum is not empty will get the same 0 value, so they can be grouped together.

Overall the query would look like this: sqlfiddle

SELECT 
    MIN(idDoc) AS idDoc
    , idProd
    , MIN(codProd) AS codProd
    , SUM(qta)
    , MIN(lotto) AS lotto
    , MIN(SNLTdett) AS SNLTdett
    , GROUP_CONCAT(DISTINCT IF(serNum='','', serNum)) AS srnSch
FROM test 
WHERE idDoc=1262 AND merce IN ('PArticoli', 'PAccessori', 'anaVcap') 
GROUP BY idProd, merce, CASE WHEN serNum='' THEN id ELSE 0 END
ORDER BY codProd;

Results

| idDoc | idProd |  codProd | SUM(qta) | lotto | SNLTdett |        srnSch |
|-------|--------|----------|----------|-------|----------|---------------|
|  1262 |    142 |  CAPSNLT |        2 |       |          |               |
|  1262 |   4069 | COMPOSTO |        2 |   AAA |          |               |
|  1262 |   4066 |   COMUNE |        2 |       |          |               |
|  1262 |   4066 |   COMUNE |        4 |       |          |               |
|  1262 |   4061 |    FIGL1 |        3 |   AAA |          |               |
|  1262 |   4062 |    FIGL2 |        3 |   BBB |          |               |
|  1262 |   4068 |    LOTTO |        8 |   AAA |          |               |
|  1262 |   4068 |    LOTTO |        4 |   BBB |          |               |
|  1262 |   4067 |  SERIALE |        7 |       |          | E,C,D,A,B,Q,Z |
|  1262 |   4063 |   SNLT-1 |        2 |       |          |       BBB,AAA |
|  1262 |   4064 |  SNLT-1B |        2 |       |          |       RRR,SSS |
user_41160170
Related