I have 2 tables:
CREATE TABLE IF NOT EXISTS `prodotti` (
`ID` smallint(3) NOT NULL,
`Denominazione` varchar(15) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `prodotti` (`ID`, `Denominazione`) VALUES
(111, 'latte'),
(222, 'uova');
CREATE TABLE IF NOT EXISTS `fornitori` (
`ID` int(3) NOT NULL,
`CF` varchar(5) NOT NULL,
`date` varchar(8) NOT NULL,
`Prezzo` int(3) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `fornitori` (`ID`, `CF`, `date`, `Prezzo`) VALUES
(111, 'AAAAA', '22/11/09', 100),
(222, 'AAAAA', '22/11/09', 200),
(222, 'BBBBB', '28/10/09', 400),
(111, 'CCCCC', '30/12/07', 100),
(222, 'CCCCC', '30/12/07', 200);
Now here is some sql query:
SELECT P.ID, COUNT(*)
FROM prodotti P,
(SELECT (SUM(Prezzo) - MAX(Prezzo)) AS T
FROM Fornitori
GROUP BY
prezzo
)F
WHERE F.T <200
GROUP BY
P.ID
But i have some question,
SELECT (
SUM( Prezzo ) - MAX( Prezzo )
) AS T
FROM Fornitori
this return 600, but why add GROUP BY prezzo, the result return 100 200 0? and what is the mean of WHERE F.T <200 thanks.
When you group by Prezzo, you'll have one row for every distinct price, aggregated from the rows in Fornitori. Then, for each row, the sum of Prezzo is computed (which is simply the sum of a single item, since you grouped by Prezzo), as is the max, and then those two are subtracted from each other. Without the GROUP BY, all rows, and thus, all prices are aggregated and you get the sum of ALL prices minus the maximum of ALL prices.
The question for you is: why are you grouping by Prezzo and then using aggregates on it? Usually, you group by one or more columns and then use aggregates on other columns (i.e., not the ones you group by). It seems to me that you probably want to be grouping by something other than Prezzo, or, if you do want to group by Prezzo (for example, to get information about products of each of the different prices), then you want to be aggregating some other column.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments