Generating order statistics grouped by order total

Question

Hopefully I can explain this correctly. I have a table of line orders (each line order consists of quantity of item and the price, there are other fields but I left those out.)

table 'orderitems':

orderid | quantity | price
1       | 1        | 1.5000
1       | 2        | 3.22
2       | 1        | 9.99
3       | 4        | 0.44
3       | 2        | 15.99

So to get order total I would run

SELECT     SUM(Quantity * price) AS total
FROM          OrderItems
GROUP BY OrderID

However, I would like to get a count of all total orders under $1 (just provide a count).

My end result I would like would be able to define ranges: under $1, $1 - $3, 3-5, 5-10, 10-15, 15.. etc;

and my data to look like so (hopefully):

tunder1 | t1to3 | t3to5 | t5to10 | etc
10      | 500   | 123   | 5633   |

So that I can present a piechart breakdown of customer orders on our eCommerce site. Now I can run individual SQL queries to get this, but I would like to know what the most efficient 'single sql query' would be. I am using MS SQL Server.

Currently I can run a single query like so to get under $1 total:

SELECT     COUNT(total) AS tunder1
FROM         (SELECT     SUM(Quantity * price) AS total
                       FROM          OrderItems
                       GROUP BY OrderID) AS a
WHERE     (total < 1)

How can I optimize this? Thanks in advance!

Answer 1

select 
  count(case when total < 1 then 1 end) tunder1,
  count(case when total >= 1 and total < 3 then 1 end) t1to3,
  count(case when total >= 3 and total < 5 then 1 end) t3to5,
  ...
from
(
  select sum(quantity * price) as total
  from orderitems group by orderid
);

Answer 2

you need to use HAVING for filtering grouped values.

Answer 3

try this:

DECLARE @YourTable table (OrderID int, Quantity int, Price decimal)
INSERT INTO @YourTable VALUES (1,1,1.5000)
INSERT INTO @YourTable VALUES (1,2,3.22)
INSERT INTO @YourTable VALUES (2,1,9.99)
INSERT INTO @YourTable VALUES (3,4,0.44)
INSERT INTO @YourTable VALUES (3,2,15.99)

SELECT
    SUM(CASE WHEN TotalCost<1 THEN 1 ELSE 0 END) AS tunder1
        ,SUM(CASE WHEN TotalCost>=1 AND TotalCost<3 THEN 1 ELSE 0 END) AS t1to3
        ,SUM(CASE WHEN TotalCost>=3 AND TotalCost<5 THEN 1 ELSE 0 END) AS t3to5
        ,SUM(CASE WHEN TotalCost>=5 THEN 1 ELSE 0 END) AS t5andup
    FROM (SELECT
              SUM(quantity * price) AS TotalCost
              FROM @YourTable
              GROUP BY OrderID
         ) dt

OUTPUT:

tunder1     t1to3       t3to5       t5andup
----------- ----------- ----------- -----------
0           0           0           3

(1 row(s) affected)

Answer 4

WITH    orders (orderid, quantity, price) AS
        (
        SELECT  1, 1, 1.5
        UNION ALL
        SELECT  1, 2, 3.22
        UNION ALL
        SELECT  2, 1, 9.99
        UNION ALL
        SELECT  3, 4, 0.44
        UNION ALL
        SELECT  4, 2, 15.99
        ),
        ranges (bound) AS
        (
        SELECT  1
        UNION ALL
        SELECT  3
        UNION ALL
        SELECT  5
        UNION ALL
        SELECT  10
        UNION ALL
        SELECT  15
        ),
        rr AS
        (
        SELECT  bound, ROW_NUMBER() OVER (ORDER BY bound) AS rn
        FROM    ranges
        ),
        r AS
        (
        SELECT  COALESCE(rf.rn, 0) AS rn, COALESCE(rf.bound, 0) AS f,
                rt.bound AS t
        FROM    rr rf
        FULL JOIN
                rr rt
        ON      rt.rn = rf.rn + 1
        )
SELECT  rn, f, t, COUNT(*) AS cnt
FROM    r
JOIN    (
        SELECT  SUM(quantity * price) AS total
        FROM    orders
        GROUP BY
                orderid
        ) o
ON      total >= f
        AND total < COALESCE(t, 10000000)
GROUP BY
        rn, t, f

Output:

rn      f       t       cnt
1       1       3       1
3       5       10      2
5       15      NULL    1

, that is 1 order from $1 to $3, 2 orders from $5 to $10, 1 order more than $15.