-- Collection of code snippets by Arne Vajhøj 
-- posted to eksperten.dk, usenet and other places (2002-now) 
CREATE TABLE ordre(ordreid INTEGER NOT NULL PRIMARY KEY,
                   navn VARCHAR(50),
                   dato DATETIME,
                   trukketbeloeb DECIMAL(12,2)
)
GO
CREATE TABLE ordrelinie(linieid INTEGER NOT NULL PRIMARY KEY,
                        ordreid INTEGER,
                        vareid INTEGER,
                        stkpris DECIMAL(12,2),
                        antal INTEGER
)
GO
INSERT INTO ordre VALUES(1,'Hans','1-2-2007',50.00)
GO
INSERT INTO ordre VALUES(2,'Jens','1-2-2007',40.00)
GO
INSERT INTO ordre VALUES(3,'Signe','2-2-2007',80.00)
GO
INSERT INTO ordre VALUES(4 ,'Frank','2-2-2007',20.00)
GO
INSERT INTO ordrelinie VALUES(1,1,123,50.00,2)
GO
INSERT INTO ordrelinie VALUES(2,1,412,20.00,3)
GO
INSERT INTO ordrelinie VALUES(3,2,382,40.00,1)
GO
INSERT INTO ordrelinie VALUES(4,3,421,35.00,3)
GO
INSERT INTO ordrelinie VALUES(5,4,482,40.00,1)
GO
INSERT INTO ordrelinie VALUES(6,4,321,20.00,1)
GO
SELECT oo.dato,
       COUNT(*),
       (SELECT SUM(antal) FROM ordrelinie WHERE ordrelinie.ordreid IN (SELECT ordreid FROM ordre io WHERE io.dato=oo.dato)),
       SUM(oo.trukketbeloeb)
FROM ordre oo
GROUP BY dato
GO
SELECT oo.dato,
       COUNT(DISTINCT oo.ordreid),
       SUM(antal),(SELECT SUM(trukketbeloeb) FROM ordre io WHERE io.dato=oo.dato)
FROM ordre oo JOIN ordrelinie ON oo.ordreid=ordrelinie.ordreid
GROUP BY dato
GO
DROP TABLE ordrelinie
GO
DROP TABLE ordre
GO
