DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

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

Hybrid View

  1. #1
    Join Date
    Mar 2008
    Posts
    164

    how build a query with some rules...

    hi,
    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
    Location
    Sterling Heights, Michigan
    Posts
    8,663
    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
    Location
    Orange, California
    Posts
    1,263
    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
    Code:
    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
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

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