with cte as(
            Select Top 5 MenuItemName AS Name, Sum(Quantity) AS Quantity ,
            Sum(Price) AS Amount
            FROM [sTMS 3].dbo.Orders
            INNER JOIN [sTMS 3].dbo.MenuItems ON [sTMS 3].dbo.Orders.MenuItemId = [sTMS 3].dbo.MenuItems.Id
            INNER JOIN [sTMS 3].dbo.Tickets TCK ON TCK.Id = [sTMS 3].dbo.Orders.TicketId
            INNER JOIN [sTMS 3].dbo.TicketEntities TEN ON TEN.Ticket_Id = TCK.Id
            WHERE 
            (MenuItems.GroupCode = 'Rides' OR MenuItems.GroupCode = 'Ticket Rides')
            AND Orders.CreatedDateTime BETWEEN Convert(DATETIME,'Jan  1 2017 12:00AM',101) and Convert(DATETIME,'Mar  3 2017 12:00AM',101)
            AND TEN.EntityCustomData NOT LIKE '%{"Name":"Card Type","Value":"Master Card"}%'
            AND TEN.EntityCustomData NOT LIKE '%{"Name":"Card Type","Value":"Technical Card"}%'
            AND TEN.EntityCustomData NOT LIKE '%{"Name":"Card Type","Value":"Guest Card"}%'
            Group by MenuItemName
            Order by Amount Desc  UNION ALL
            Select Top 5 MenuItemName AS Name, Sum(Quantity) AS Quantity ,
            Sum(Price) AS Amount
            FROM sTMS10.dbo.Orders
            INNER JOIN sTMS10.dbo.MenuItems ON sTMS10.dbo.Orders.MenuItemId = sTMS10.dbo.MenuItems.Id
            INNER JOIN sTMS10.dbo.Tickets TCK ON TCK.Id = sTMS10.dbo.Orders.TicketId
            INNER JOIN sTMS10.dbo.TicketEntities TEN ON TEN.Ticket_Id = TCK.Id
            WHERE 
            (MenuItems.GroupCode = 'Rides' OR MenuItems.GroupCode = 'Ticket Rides')
            AND Orders.CreatedDateTime BETWEEN Convert(DATETIME,'Jan  1 2017 12:00AM',101) and Convert(DATETIME,'Mar  3 2017 12:00AM',101)
            AND TEN.EntityCustomData NOT LIKE '%{"Name":"Card Type","Value":"Master Card"}%'
            AND TEN.EntityCustomData NOT LIKE '%{"Name":"Card Type","Value":"Technical Card"}%'
            AND TEN.EntityCustomData NOT LIKE '%{"Name":"Card Type","Value":"Guest Card"}%'
            Group by MenuItemName
            Order by Amount Desc)
            Select top 5 Name,Sum(Quantity) AS Quantity,Sum(Amount)/``
            (Select SUM(Amount) FROM (Select top 5 Sum(e.Amount) Amount From cte e Group by e.Name order by e.Amount desc)a) * 100 AS Percentage,Sum(Amount) AS Amount 
            ,CONVERT(DATETIME,'Jan  1 2017 12:00AM',101) FromDate
            ,CONVERT(DATETIME,'Mar  3 2017 12:00AM',101) ToDate     
            From cte Group by Name order by Amount desc

La columna "cte.Amount" no es válida en la cláusula ORDER BY porque no está contenida ni en una función agregada ni en la cláusula GROUP BY.

0
farooq shahid 1 abr. 2017 a las 11:47

2 respuestas

La mejor respuesta

Solo está ordenando por e.Amount en lugar de la columna agregada SUM(Amount) Amount en la cuarta fila de la última:

with cte as(
        Select Top 5 MenuItemName AS Name, Sum(Quantity) AS Quantity ,
        Sum(Price) AS Amount
        FROM [sTMS 3].dbo.Orders
        INNER JOIN [sTMS 3].dbo.MenuItems ON [sTMS 3].dbo.Orders.MenuItemId = [sTMS 3].dbo.MenuItems.Id
        INNER JOIN [sTMS 3].dbo.Tickets TCK ON TCK.Id = [sTMS 3].dbo.Orders.TicketId
        INNER JOIN [sTMS 3].dbo.TicketEntities TEN ON TEN.Ticket_Id = TCK.Id
        WHERE 
        (MenuItems.GroupCode = 'Rides' OR MenuItems.GroupCode = 'Ticket Rides')
        AND Orders.CreatedDateTime BETWEEN Convert(DATETIME,'Jan  1 2017 12:00AM',101) and Convert(DATETIME,'Mar  3 2017 12:00AM',101)
        AND TEN.EntityCustomData NOT LIKE '%{"Name":"Card Type","Value":"Master Card"}%'
        AND TEN.EntityCustomData NOT LIKE '%{"Name":"Card Type","Value":"Technical Card"}%'
        AND TEN.EntityCustomData NOT LIKE '%{"Name":"Card Type","Value":"Guest Card"}%'
        Group by MenuItemName
        Order by Amount Desc  UNION ALL
        Select Top 5 MenuItemName AS Name, Sum(Quantity) AS Quantity ,
        Sum(Price) AS Amount
        FROM sTMS10.dbo.Orders
        INNER JOIN sTMS10.dbo.MenuItems ON sTMS10.dbo.Orders.MenuItemId = sTMS10.dbo.MenuItems.Id
        INNER JOIN sTMS10.dbo.Tickets TCK ON TCK.Id = sTMS10.dbo.Orders.TicketId
        INNER JOIN sTMS10.dbo.TicketEntities TEN ON TEN.Ticket_Id = TCK.Id
        WHERE 
        (MenuItems.GroupCode = 'Rides' OR MenuItems.GroupCode = 'Ticket Rides')
        AND Orders.CreatedDateTime BETWEEN Convert(DATETIME,'Jan  1 2017 12:00AM',101) and Convert(DATETIME,'Mar  3 2017 12:00AM',101)
        AND TEN.EntityCustomData NOT LIKE '%{"Name":"Card Type","Value":"Master Card"}%'
        AND TEN.EntityCustomData NOT LIKE '%{"Name":"Card Type","Value":"Technical Card"}%'
        AND TEN.EntityCustomData NOT LIKE '%{"Name":"Card Type","Value":"Guest Card"}%'
        Group by MenuItemName
        Order by Amount Desc)
        Select top 5 Name,Sum(Quantity) AS Quantity,Sum(Amount)/``
        (Select SUM(Amount) FROM (Select top 5 Sum(e.Amount) Amount From cte e Group by e.Name order by Amount desc)a) * 100 AS Percentage,Sum(Amount) AS Amount 
        ,CONVERT(DATETIME,'Jan  1 2017 12:00AM',101) FromDate
        ,CONVERT(DATETIME,'Mar  3 2017 12:00AM',101) ToDate     
        From cte Group by Name order by Amount desc
0
Abdullah Dibas 1 abr. 2017 a las 09:40

No puede UNION ALL las declaraciones TOP así, puede ponerlas en Common Table Expressions y luego UNION ALL por separado.

Entonces, algo como esto, aunque puede que necesite envolver el UNION ALL en otro CTE para poder hacer otro más TOP 5

WITH FirstTop5 AS
(
SELECT TOP 5
        MenuItemName AS Name
    ,   SUM(Quantity) AS Quantity
    ,   SUM(Price) AS Amount
    FROM [sTMS 3].dbo.Orders
    INNER JOIN [sTMS 3].dbo.MenuItems
        ON [sTMS 3].dbo.Orders.MenuItemId = [sTMS 3].dbo.MenuItems.id
    INNER JOIN [sTMS 3].dbo.Tickets TCK
        ON TCK.id = [sTMS 3].dbo.Orders.TicketId
    INNER JOIN [sTMS 3].dbo.TicketEntities TEN
        ON TEN.Ticket_Id = TCK.id
    WHERE (MenuItems.GroupCode = 'Rides'
    OR MenuItems.GroupCode = 'Ticket Rides')
    AND Orders.CreatedDateTime BETWEEN CONVERT(DATETIME, 'Jan  1 2017 12:00AM', 101) AND CONVERT(DATETIME, 'Mar  3 2017 12:00AM', 101)
    AND TEN.EntityCustomData NOT LIKE '%{"Name":"Card Type","Value":"Master Card"}%'
    AND TEN.EntityCustomData NOT LIKE '%{"Name":"Card Type","Value":"Technical Card"}%'
    AND TEN.EntityCustomData NOT LIKE '%{"Name":"Card Type","Value":"Guest Card"}%'
    GROUP BY MenuItemName
    ORDER BY Amount DESC
)
, SecondTop5 AS
(
    SELECT TOP 5
        MenuItemName AS Name
    ,   SUM(Quantity) AS Quantity
    ,   SUM(Price) AS Amount
    FROM sTMS10.dbo.Orders
    INNER JOIN sTMS10.dbo.MenuItems
        ON sTMS10.dbo.Orders.MenuItemId = sTMS10.dbo.MenuItems.id
    INNER JOIN sTMS10.dbo.Tickets TCK
        ON TCK.id = sTMS10.dbo.Orders.TicketId
    INNER JOIN sTMS10.dbo.TicketEntities TEN
        ON TEN.Ticket_Id = TCK.id
    WHERE (MenuItems.GroupCode = 'Rides'
    OR MenuItems.GroupCode = 'Ticket Rides')
    AND Orders.CreatedDateTime BETWEEN CONVERT(DATETIME, 'Jan  1 2017 12:00AM', 101) AND CONVERT(DATETIME, 'Mar  3 2017 12:00AM', 101)
    AND TEN.EntityCustomData NOT LIKE '%{"Name":"Card Type","Value":"Master Card"}%'
    AND TEN.EntityCustomData NOT LIKE '%{"Name":"Card Type","Value":"Technical Card"}%'
    AND TEN.EntityCustomData NOT LIKE '%{"Name":"Card Type","Value":"Guest Card"}%'
    GROUP BY MenuItemName
    ORDER BY Amount DESC
)

SELECT * FROM FirstTop5
UNION ALL
SELECT * FROM SecondTop5

Sin embargo, podría ser mejor eliminar la restricción TOP 5 de su CTE y simplemente hacer un UNION ALL y luego hacer el TOP 5 en su consulta externa.

0
mheptinstall 1 abr. 2017 a las 09:25