tags:

views:

38

answers:

6

I am trying to suma column in a table, while excluding certain records that have the paid field set to true.

I am doing so like this:

SELECT SUM( cost ) AS total
FROM sales
WHERE passport = 'xxxxx'
AND paid <>1

The table is full of data, and I can display costs by themselves, or the entire total. Just adding on AND paid <>1 Is what causes it to fail. The query does not fail as such, but NULL is returned which is quite useless.

This is the SQL for my table

CREATE TABLE sales (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  uuid varchar(64) NOT NULL,
  firstname varchar(64) NOT NULL DEFAULT '',
  lastname varchar(64) NOT NULL DEFAULT '',
  passport varchar(64) DEFAULT NULL,
  product varchar(64) NOT NULL,
  quantity int(11) DEFAULT NULL,
  cost double DEFAULT NULL,
  paymenttype varchar(64) NOT NULL DEFAULT '',
  paid tinyint(1) DEFAULT NULL,
  tabno varchar(64) NOT NULL,
  createdby int(10) unsigned DEFAULT NULL,
  creationdate datetime DEFAULT NULL,
  modifiedby int(10) unsigned DEFAULT NULL,
  modifieddate timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
)

And the current data

INSERT INTO sales (id, uuid, firstname, lastname, passport, product, quantity, cost, paymenttype, paid, tabno, createdby, creationdate, modifiedby, modifieddate) VALUES
(20, ':8dcee958-d1ac-6791-6253-0a7344054295', 'Jason', 'Hoff', 'r454545', 'Nicaraguan nachoes', 4, 320, 'credit', 1, '23434', 2, '2010-07-06 04:10:18', 2, '2010-07-06 04:10:18'),
(19, ':3f03cda5-21bf-9d8c-5eaa-664eb2d4f5a6', 'Jason', 'Hoff', 'r454545', 'Nica Libre (doble 4 o 5 anos)', 1, 30, 'cash', NULL, '35', 2, '2010-07-06 03:35:35', 2, '2010-07-06 03:35:35'),
(18, ':f83da33b-2238-94b9-897c-debed0c3815e', 'Jason', 'Hoff', 'r454545', 'Helado con salsa de chocolate', 1, 40, 'cash', 1, '2', 2, '2010-07-05 21:30:58', 2, '2010-07-05 21:30:58');
A: 

This may mean there are no rows in the table for which paid <> 1. Or maybe there are but they cost of NULL.

Brian Hooper
no costs are null, and there are records where paid is not set to one.
Jacob
You don't have a passport of 'xxxxx'. Presumably that wasn't the real value? Also, you have a NULL in the paid column. That might lead to trouble if you plan to use it as a boolean type.
Brian Hooper
A: 

Hi, I would not use "<>", but use "!=" instead;

SELECT SUM( cost ) AS total
FROM sales
WHERE passport = 'xxxxx'
AND paid != 1

If that doesn't work, can you post the table definition? And are there any records which do not have paid = 1?

Dave Rix
yes, there is one record where paid is not set to one. Using the above, only NULL is still returned.
Jacob
`<>` is standard SQL. `!=` is supported by some, but by no means all, databases, and is not standard.
psmears
A: 
SELECT SUM( cost ) AS total
FROM sales
WHERE passport = 'xxxxx'
AND paid = false or paid is null

EDITED

use

IS NOT 

instead of

!=

This should work

SELECT SUM( cost ) AS total
FROM sales
WHERE passport = 'xxxxx'
AND paid IS NOT true
Salil
Hmm, this still returns NULL. I have edited my question to show the contents of the table.
Jacob
@Jacob:- because in all your data paid is set to TRUE
Salil
Salil, the middle record has paid set to NULL.
Jacob
@jacob:- check my EDITED answer i hope that helps :)
Salil
works beautifully, thankyou
Jacob
+2  A: 

The 'paid' value is NULL for that row. You would need to do

SELECT SUM( cost ) AS total
FROM test.sales
WHERE passport = 'r454545'
AND paid IS NULL or paid = 0 
     /*Or paid <> 1 as I see you are using tinyint datatype*/

Or, better would be to not allow NULLS in that column and have paid default to 0.

Martin Smith
+1  A: 

Your problem is that your condition does not match any rows.

The condition paid <> 1 does not match the row where paid is NULL.

Try this query: SELECT 1 <> NULL It will return NULL. A WHERE clause filters out rows in which the clause is either false or NULL.

Replace AND paid <> 1 with AND (paid IS NULL OR paid <> 1)

Hammerite
A: 

The book SQL Antipatterns describes this problem in detail, section Searching Nullable Columns. Strongly recommended book.

sarnold