DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: how build a query with some rules...

  1. #1
    Join Date
    Mar 2008

    how build a query with some rules...

    have this rows:

    id , point, id_vendedor

    if i want know how many counts the id_vendedor i do this:
    SELECT id_vendedor, COUNT(id_vendedor) FROM
    table where id_vendedor like '84' GROUP BY id_vendedor

    and they return this: vendedor [id] have 5 records in total
    so far so good but now i want creat a sub-count that say this:

    vendedor x have 5 records in total ( like he say now)
    and have 3 point = 1 and 2 point =0
    how can i do that?
    thanks for your help

  2. #2
    Join Date
    Apr 2007
    Sterling Heights, Michigan
    Quote Originally Posted by jason213123
    vendedor x have 5 records in total ( like he say now)
    and have 3 point = 1 and 2 point =0
    I don't understand this part. What do you need to get from here?

  3. #3
    Join Date
    Aug 2004
    Orange, California
    Don't know what database you are using so you may need to change the Abs() function to one that works for your database.
    Abs() means absolute value, so all negative values become positive.
    If Point = 0 then on most systems -1 is returned for True and zero for False.
    Abs(Point = 0) forces True to be 1 and False will still be zero.
    This way only True values get summed. So If Point = 0 then it will be Summed under the Point0 column; but If Point = 1 then it will be summed under the Point1 column.
    Now if Point is always a 1 or a zero then you only need to do a Sum(Point) for the Point1 column, but you still need the Sum(Abs(Point = 0)) for the Point0 column
    SELECT id_vendedor, COUNT(id_vendedor), 
    Sum(Abs(Point = 0)) As Point0, Sum(Abs(Point = 1)) As Point1
    FROM table 
    WHERE id_vendedor Like '84' 
    GROUP BY id_vendedor

Similar Threads

  1. best way to build a query
    By jason213123 in forum Database
    Replies: 2
    Last Post: 07-02-2008, 06:10 AM
  2. Union query
    By peljo in forum Database
    Replies: 4
    Last Post: 12-17-2007, 12:38 PM
  3. SQL2000 remote SQL query
    By lightningtechie in forum Database
    Replies: 1
    Last Post: 02-07-2006, 09:34 AM
  4. Help me out to build the query
    By ganesh in forum Database
    Replies: 0
    Last Post: 12-10-2001, 11:57 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
HTML5 Development Center
Latest Articles
Questions? Contact us.
Web Development
Latest Tips
Open Source

   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center