Professional Web Applications Themes

Help with simple GROUP BY query... - MySQL

Hi, I've got a table of data with 3 fields as follows: SupplierID, ProductID, Price 1, 1, 1.50 2, 1, 2.50 3, 1, 3.50 1, 2, 1.20 etc. Important point to note is that for each value of ProductID there are multiple records. What I want to do is the following: 1. Get a count of the number of unique ProductIDs for which there is no SupplierIDs offering prices below some value. I thought this would work, but it does not: SELECT COUNT(*) FROM Inventory WHERE MIN(Price)>'1.49' GROUP BY ProductID I get "invalid use of group function". 2. Get a ...

  1. #1

    Default Help with simple GROUP BY query...

    Hi,

    I've got a table of data with 3 fields as follows:

    SupplierID, ProductID, Price
    1, 1, 1.50
    2, 1, 2.50
    3, 1, 3.50
    1, 2, 1.20

    etc.

    Important point to note is that for each value of ProductID there are
    multiple records.

    What I want to do is the following:

    1. Get a count of the number of unique ProductIDs for which there is
    no SupplierIDs offering prices below some value. I thought this would
    work, but it does not:

    SELECT COUNT(*) FROM Inventory WHERE MIN(Price)>'1.49' GROUP BY
    ProductID

    I get "invalid use of group function".


    2. Get a handle onto all of the products that match. I've been using
    this:

    SELECT *,MIN(Price) as MinPrice FROM Inventory WHERE MinPrice>'1.49'
    GROUP BY ProductID

    I get "invalid use of group function".


    Thanks in advance,

    Aetherweb Guest

  2. #2

    Default Re: Help with simple GROUP BY query...

    Aetherweb <com> wrote:
     


    Does this query help you?

    SELECT * FROM
    (
    SELECT x.SupplierID as 'supplier',
    x.ProductID as 'product',
    y.SupplierID as 'cheaper',
    x.Price
    FROM inventory x
    LEFT JOIN Inventory y
    ON y.ProductID = x.ProductID
    AND x.Price > y.Price
    ) z
    WHERE z.cheaper IS NULL;
    --
    Rik Wasmus
    Posted on Usenet, not any forum you might see this in.
    Ask Smart Questions: http://tinyurl.com/anel
    Rik Guest

  3. #3

    Default Re: Help with simple GROUP BY query...

    On Mar 8, 4:44 pm, Rik <com> wrote: 











    >
    > Does this query help you?
    >
    > SELECT * FROM
    > (
    > SELECT x.SupplierID as 'supplier',
    > x.ProductID as 'product',
    > y.SupplierID as 'cheaper',
    > x.Price
    > FROM inventory x
    > LEFT JOIN Inventory y
    > ON y.ProductID = x.ProductID
    > AND x.Price > y.Price
    > ) z
    > WHERE z.cheaper IS NULL;
    > --
    > Rik Wasmus
    > Posted on Usenet, not any forum you might see this in.
    > Ask Smart Questions:http://tinyurl.com/anel[/ref]

    That does help - makes it clear that my simple problem isn't as simple
    as it first appears. Problem is that I need this to crunch quite a
    large amount of data very quickly, and it runs quite often. I think
    I'll have to create a lookup table and query that instead.

    Thanks for your help.

    Aetherweb Guest

  4. #4

    Default Re: Help with simple GROUP BY query...

    Aetherweb <com> wrote: 
    >> 
    >> 
    >> 
    >> 
    >> 
    >> 
    >>
    >> Does this query help you?
    >>
    >> SELECT * FROM
    >> (
    >> SELECT x.SupplierID as 'supplier',
    >> x.ProductID as 'product',
    >> y.SupplierID as 'cheaper',
    >> x.Price
    >> FROM inventory x
    >> LEFT JOIN Inventory y
    >> ON y.ProductID = x.ProductID
    >> AND x.Price > y.Price
    >> ) z
    >> WHERE z.cheaper IS NULL;
    >> --
    >> Rik Wasmus
    >> Posted on Usenet, not any forum you might see this in.
    >> Ask Smart Questions:http://tinyurl.com/anel[/ref]
    >
    > That does help - makes it clear that my simple problem isn't as simple
    > as it first appears. Problem is that I need this to crunch quite a
    > large amount of data very quickly, and it runs quite often. I think
    > I'll have to create a lookup table and query that instead.[/ref]

    Don not assume that based on my response. Lack of knowledge often makes my
    queries (seem) more complex then need be, there might be quicker solutions
    out there.
    --
    Rik Wasmus
    Posted on Usenet, not any forum you might see this in.
    Ask Smart Questions: http://tinyurl.com/anel
    Rik Guest

Similar Threads

  1. cfgrid inside a <cfoutput query="myQuery" group="GROUP">
    By DavidGhous in forum Coldfusion Flash Integration
    Replies: 1
    Last Post: April 12th, 07:23 PM
  2. Separate query with group by
    By Seth Buntin in forum Coldfusion Database Access
    Replies: 3
    Last Post: February 22nd, 07:00 PM
  3. NDS user and group query
    By Michael Weber in forum PERL Beginners
    Replies: 2
    Last Post: February 13th, 10:48 PM
  4. XP Simple File Sharing and Group Policy
    By Dave Branscome in forum Windows Setup, Administration & Security
    Replies: 0
    Last Post: July 11th, 11:43 PM
  5. GROUP BY?? Simple!
    By Stephen McCormack in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 3rd, 11:44 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139