Reputation: 717
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
Upvotes: 2
Views: 1239
Reputation: 32685
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 |
Upvotes: 1