Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2012
    Posts
    2

    Red face Unanswered: problem filtering

    I recently started learning sql. i'm working on a exercise and now i'm stuck. I've spent hours searching the web but i couldnt find any answers

    here's my work:

    select dbo.Products.ProductName,
    SUM (dbo.[order details].unitprice * dbo.[order details].Quantity) as Amount
    from dbo.[Order Details]
    inner join dbo.Orders on dbo.[Order Details].OrderID = dbo.Orders.OrderID
    inner join dbo.Products on dbo.[Order Details].ProductID = dbo.Products.ProductID
    where dbo.orders.orderdate
    between '1997-01-01 00:00:00.000' and '1997-12-31 00:00:00.000' --and Amount >= 15000.00
    group by dbo.Products.ProductName
    order by dbo.Products.ProductName desc




    my problem is i'm trying to filter the "Amount" so only ones with a "Amount" greater than or equal to # will display.


    thanks

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    This should work
    Code:
    select dbo.Products.ProductName,
        SUM (dbo.[order details].unitprice * dbo.[order details].Quantity) as Amount
    from dbo.[Order Details]
        inner join dbo.Orders on 
            dbo.[Order Details].OrderID = dbo.Orders.OrderID
        inner join dbo.Products on 
            dbo.[Order Details].ProductID = dbo.Products.ProductID
    where dbo.orders.orderdate between '1997-01-01 00:00:00.000' and '1997-12-31 00:00:00.000' 
    group by dbo.Products.ProductName
    HAVING SUM (dbo.[order details].unitprice * dbo.[order details].Quantity) >= 15000.00
    order by dbo.Products.ProductName desc
    The HAVING clause works on the aggerated data of the GROUP BY.

    Also handy when you want to select on groups, based on the amount of records:

    Find all duplicates in a table:

    SELECT ....
    FROM...
    WHERE...
    GROUP BY aUniqueColumn
    HAVING COUNT(*) > 1


    Select the smallest football teams

    SELECT FootballTeam.Name, COUNT(*) as NbrOfMembers
    FROM ...
    WHERE...
    GROUP BY FootballTeamName
    HAVING COUNT(*) < 100
    Last edited by Wim; 02-10-12 at 10:43.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Feb 2012
    Posts
    2
    Yea it worked. Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •