[SQL] Verkopen per product, per maand

[_Piwi_]

Ik wil een report uitdraaien van een webwinkel met 2 tabellen:
1. order
2. orderline

Als resultaat wil ik een tabel waarin ik per maand kan zien hoeveel ik van elk product verkocht heb.
Het gedeelte dat ik elke maand krijg werkt. Alleen gaat het mis met de inner join en kom er ook niet uit. Ik hoop dat iemand wel een ingeving heeft en mij verder kan helpen.

Huidige SQL query (die dus niet 100 % goed werkt):

SQL:

SELECT
    `shop_orderline`.ItemNo_ AS Productnummer,
    `shop_orderline`.Quantity AS Verkocht,
    MONTHNAME(`shop_order`.OrderDate) AS Maand,
    YEAR(`shop_order`.OrderDate) AS Jaar
FROM `shop_order`
        LEFT JOIN (
            SELECT 
                FORMAT(SUM(`shop_orderline`.Quantity), 2) AS Quantity,
                `shop_orderline`.ItemNo_,
                `shop_orderline`.OrderNo_
            FROM `shop_orderline`
            WHERE `shop_orderline`.ItemNo_ != 'VERZEND' AND `shop_orderline`.ItemNo_ != 'BETAAL' AND (`shop_orderline`.ItemNo_ != '' OR `shop_orderline`.ItemNo_ != NULL)
            GROUP BY `shop_orderline`.ItemNo_
        ) AS `shop_orderline`
        ON `shop_orderline`.OrderNo_ = `shop_order`.No_
GROUP BY YEAR(`shop_order`.Orderdate), MONTH(`shop_order`.OrderDate)
ORDER BY YEAR(`shop_order`.OrderDate), MONTH(`shop_order`.OrderDate), `shop_orderline`.ItemNo_

Alvast bedankt

[ Voor 2% gewijzigd door BtM909 op 31-07-2012 16:28 ]

[The Eagle]

Als je wilt groperen op twee velden, moet je ook twee grouperingen invoeren. Ik mis de group by op itemno

[_Piwi_]

Die staat in de Inner Join (waar hij niet hoort). Maar ook buiten de Inner Join werkt het niet

EDIT
Oh wat stom ook... Ik had de SUM gewoon verkeerd staan, ja dan werkt het inderdaad niet

SQL:

SELECT
    `shop_orderline`.ItemNo_ AS Productnummer,
    SUM(`shop_orderline`.Quantity) AS Verkocht,
    MONTHNAME(`shop_order`.OrderDate) AS Maand,
    YEAR(`shop_order`.OrderDate) AS Jaar
FROM `shop_order`
        LEFT JOIN (
            SELECT 
                `shop_orderline`.Quantity,
                `shop_orderline`.ItemNo_,
                `shop_orderline`.OrderNo_
            FROM `shop_orderline`
            WHERE `shop_orderline`.ItemNo_ != 'VERZEND' AND `shop_orderline`.ItemNo_ != 'BETAAL' AND (`shop_orderline`.ItemNo_ != '' OR `shop_orderline`.ItemNo_ != NULL)
        ) AS `shop_orderline`
        ON `shop_orderline`.OrderNo_ = `shop_order`.No_
GROUP BY YEAR(`shop_order`.Orderdate), MONTH(`shop_order`.OrderDate), `shop_orderline`.ItemNo_
ORDER BY YEAR(`shop_order`.OrderDate), MONTH(`shop_order`.OrderDate), `shop_orderline`.ItemNo_

Voor diegene die hier ook ooit tegenaan lopen.

[ Voor 83% gewijzigd door BtM909 op 31-07-2012 16:29 ]

[NMe]

Waarom überhaupt die subquery? Wat je hier doet kan toch in één query? Verder inderdaad die group by's: als je een kolom selecteert moet je er ook op groeperen, tenzij het een aggregate function is. Dat ben je in dit geval zowel bij je subquery als in je outer query vergeten.

[Onbekend]

superbig91 schreef op dinsdag 31 juli 2012 @ 15:26:
Als resultaat wil ik een tabel waarin ik per maand kan zien hoeveel ik van elk product verkocht heb.
Het gedeelte dat ik elke maand krijg werkt. Alleen gaat het mis met de inner join en kom er ook niet uit. Ik hoop dat iemand wel een ingeving heeft en mij verder kan helpen.

Ik heb even de belangrijkste punten vet gemaakt. Het gaat om de producten, dus eerst de tabel Orderline uitlezen, en dan inner join met Order.

En uiteraard een group by (of sum) op de vetgedrukte velden.

[_Piwi_]

@Heren hierboven,
Ik dacht ook al verkeerd om bezig te zijn maar het is op deze manier ook gelukt.

@NMe,
Ik ben benieuwd hoe jij het zou doen. Als je tijd heb zag ik graag een voorbeeldje

[ Voor 3% gewijzigd door _Piwi_ op 31-07-2012 15:52 ]

[NMe]

superbig91 schreef op dinsdag 31 juli 2012 @ 15:52:
@NMe,
Ik ben benieuwd hoe jij het zou doen. Als je tijd heb zag ik graag een voorbeeldje

Vooruit.

SQL:

SELECT
    shop_orderline.ItemNo_ AS Productnummer,
    SUM(shop_orderline.Quantity) AS Verkocht,
    MONTH(shop_order.OrderDate) AS Maand,
    YEAR(shop_order.OrderDate) AS Jaar
FROM shop_order LEFT JOIN shop_orderline ON shop_orderline.OrderNo_ = shop_order.No_
WHERE shop_orderline.ItemNo_ NOT IN ('VERZEND', 'BETAAL', '') AND orderline.ItemNo_ IS NOT NULL
GROUP BY shop_orderline.ItemNo_, YEAR(shop_order.Orderdate), MONTH(shop_order.OrderDate)
ORDER BY Jaar, Maand, Productnummer

Ongetest, maar goed. Overigens: niet MONTHNAME gebruiken maar gewoon MONTH. Je presentatielaag kan dat wel formatten voor je, dat moet je niet door je DB laten doen.

[_Piwi_]

Die is inderdaad een stuk mooier (en waarschijnlijk ook sneller). De NOT IN functie kon ik nog niet dus ook weer wat geleerd.
Dat ik MONTHNAME gebruikt heb komt omdat de data direct in een PDF wordt gegoten (lui he).

[NMe]

Alsnog niet netjes.

Overigens zie ik nu dat je != NULL gebruikt om te kijken of iets NULL is of niet. Dat werkt niet, vergelijkingen met NULL moet je uitschrijven, dus IS NOT NULL. NULL is per definitie nergens gelijk aan, ook niet aan zichzelf.