MySQL Forums
Forum List  »  German

Re: SUM() später in WHERE-Klausel ?
Posted by: Mark Knochen
Date: April 13, 2012 04:53AM

Hallo Thomas,

eigentlich geht es wieder um die "Riesenabfrage" von vor einiger Zeit, das Beispiel hier kürze ich mal herunter. Zuerst die Tabellen mit Beispieldaten:

--
-- Tabellenstruktur für Tabelle `invoicelines`
--

CREATE TABLE IF NOT EXISTS `invoicelines` (
  `itemId` int(11) NOT NULL,
  `qty` int(3) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Daten für Tabelle `invoicelines`
--

INSERT INTO `invoicelines` (`itemId`, `qty`) VALUES
(2, 2),
(2, 3),
(3, 1),
(3, 2),
(4, 2),
(4, 2),
(5, 1),
(5, 2),
(6, 1),
(6, 1);

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `items`
--

CREATE TABLE IF NOT EXISTS `items` (
  `ItemName` varchar(10) NOT NULL,
  `itemId` int(11) NOT NULL,
  `id_gender` int(11) NOT NULL,
  `brand` varchar(20) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Daten für Tabelle `items`
--

INSERT INTO `items` (`ItemName`, `itemId`, `id_gender`, `brand`) VALUES
('Suit1', 1, 1, 'Suit'),
('Suit2', 2, 2, 'Suit'),
('Suit3', 3, 1, 'Suit'),
('Test1', 4, 1, 'Test'),
('Test2', 5, 2, 'Test'),
('Test3', 6, 1, 'Test');

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `items_stock`
--

CREATE TABLE IF NOT EXISTS `items_stock` (
  `itemStockId` int(11) NOT NULL,
  `itemId` int(11) NOT NULL,
  `brand` varchar(20) NOT NULL,
  `PhysicalStock` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Daten für Tabelle `items_stock`
--

INSERT INTO `items_stock` (`itemStockId`, `itemId`, `brand`, `PhysicalStock`) VALUES
(1, 1, 'Suit', 10),
(2, 1, 'Suit', 5),
(3, 2, 'Suit', 20),
(4, 2, 'Suit', 5),
(5, 3, 'Suit', 10),
(6, 3, 'Suit', 5),
(7, 6, 'Test', 7),
(8, 6, 'Test', 8),
(9, 4, 'Test', 9),
(10, 4, 'Test', 10),
(11, 5, 'Test', 11),
(12, 5, 'Test', 12);


Mit folgendem Select soll die Reichweite - also, wie lange ein Produkt braucht, um verkauft zu werden, bezogen auf eine bestimmte Zeitspanne - mit ausgegeben werden (ich habe in dem Fall nur 1 Tag als Zeitspanne genommen, live würde das dynamisch anhand eines Zeitraumes berechnet werden)


SELECT * ,

IF((
(

SUM(
(SELECT SUM(items_stock.PhysicalStock)
FROM items_stock
WHERE items_stock.itemId = items.itemId))

) / (SUM(IF(invoicelines.qty > 0, invoicelines.qty, 0)) / (1))) IS NULL, 0, (
(

SUM(
(SELECT SUM(items_stock.PhysicalStock)
FROM items_stock
WHERE items_stock.itemId = items.itemId))

) / (SUM(IF(invoicelines.qty > 0, invoicelines.qty, 0)) / (1)))) AS rangeDays

FROM items
LEFT JOIN(SELECT DISTINCT itemId, SUM(qty) AS qty
FROM invoicelines GROUP BY itemId ORDER BY NULL) invoicelines ON(invoicelines.itemId = items.itemId) GROUP BY items.itemID


+----------+--------+-----------+-------+--------+------+-----------+
| ItemName | itemId | id_gender | brand | itemId | qty  | rangeDays |
+----------+--------+-----------+-------+--------+------+-----------+
| Suit1    |      1 |         1 | Suit  |   NULL | NULL |         0 |
| Suit2    |      2 |         2 | Suit  |      2 |    5 |    5.0000 |
| Suit3    |      3 |         1 | Suit  |      3 |    3 |    5.0000 |
| Test1    |      4 |         1 | Test  |      4 |    4 |    4.7500 |
| Test2    |      5 |         2 | Test  |      5 |    3 |    7.6667 |
| Test3    |      6 |         1 | Test  |      6 |    2 |    7.5000 |
+----------+--------+-----------+-------+--------+------+-----------+
6 rows in set (0.00 sec)


Nun soll per Filter auch möglich sein, nur die "items" zu selektieren, die z.B. einen Wert von rangeDays BETWEEN '4' AND '5' haben



SELECT * ,

IF((
(

SUM(
(SELECT SUM(items_stock.PhysicalStock)
FROM items_stock
WHERE items_stock.itemId = items.itemId))

) / (SUM(IF(invoicelines.qty > 0, invoicelines.qty, 0)) / (1))) IS NULL, 0, (
(

SUM(
(SELECT SUM(items_stock.PhysicalStock)
FROM items_stock
WHERE items_stock.itemId = items.itemId))

) / (SUM(IF(invoicelines.qty > 0, invoicelines.qty, 0)) / (1)))) AS rangeDays

FROM items
LEFT JOIN(SELECT DISTINCT itemId, SUM(qty) AS qty
FROM invoicelines GROUP BY itemId ORDER BY NULL) invoicelines ON(invoicelines.itemId = items.itemId) GROUP BY items.itemID 
HAVING rangeDays BETWEEN '4' AND '5'


+----------+--------+-----------+-------+--------+------+-----------+
| ItemName | itemId | id_gender | brand | itemId | qty  | rangeDays |
+----------+--------+-----------+-------+--------+------+-----------+
| Suit2    |      2 |         2 | Suit  |      2 |    5 |    5.0000 |
| Suit3    |      3 |         1 | Suit  |      3 |    3 |    5.0000 |
| Test1    |      4 |         1 | Test  |      4 |    4 |    4.7500 |
+----------+--------+-----------+-------+--------+------+-----------+
3 rows in set (0.01 sec)


Der SELECT funktioniert prima, wenn man auf die einzelnen "items" gruppiert.
Wenn ich aber nach items.brand gruppiere, dann bekomme ich kein Ergebnis zurück, da sich die HAVING Klausel auf die berechneten und schon gruppierten Werte von rangeDays bezieht.


SELECT *,
         
         IF((
    		(
    			
      SUM(
            (SELECT SUM(items_stock.PhysicalStock)
              FROM items_stock
             WHERE items_stock.itemId = items.itemId 
           )
      )
      
    		) / ( SUM(IF(invoicelines.qty > 0,invoicelines.qty,0)) / ( 1 ) )
    	) IS NULL,0,(
    		(
    			
      SUM(
            (SELECT SUM(items_stock.PhysicalStock)
              FROM items_stock
             WHERE items_stock.itemId = items.itemId 
           )
      )
      
    		) / ( SUM(IF(invoicelines.qty > 0,invoicelines.qty,0)) / ( 1 ) )
    	)) AS rangeDays
        
        FROM items 
        LEFT JOIN ( SELECT DISTINCT itemId,SUM(qty) AS qty 
                FROM invoicelines GROUP BY itemId ORDER BY NULL ) invoicelines ON (invoicelines.itemId = items.itemId)  GROUP BY items.brand HAVING rangeDays BETWEEN '4' AND '5'

sollte also eigentlich beide "brands" listen, mit den Mittelwerten aus den enthaltenen "items", die rangeDays BETWEEN '4' AND '5' haben.


Puh, etwas länger geworden ... wie gesagt, das ganze ist eigentlich wieder Teil der doch sehr komplexen Abrage aus

http://forums.mysql.com/read.php?70,510668,514338#msg-514338

und sollte nicht weiter an Zeit fressen :)

Danke schonmal


Mark

Options: ReplyQuote


Subject
Views
Written By
Posted
2227
April 11, 2012 06:18AM
Re: SUM() später in WHERE-Klausel ?
1195
April 13, 2012 04:53AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.