CODEWITHSUNDEEP
phpmysqlselect
AdamMc
AdamMc

Reputation: 205

Select query that excludes a particular value within a table

I have a query which counts how many rows that have the value '1234' in the 'dealer_code' field.

This works fine however I do not want to include rows with a 'sales_id' which contains or ends with a value of '.D999'

For example, if the sales_id in the database contains this:

sales_id  |  dealer_code
123.D000  |  1234          x
123.D777  |  1234          x
123.D333  |  5555 
123.D999  |  1234 
123.D999  |  5555  
123.D111  |  1234          x
123.D999  |  5555

The code below should bring up a result of 3 but it says 4. Can anyone help?

$total_query = "SELECT COUNT(sales_points) AS total_sales_points 
FROM sales_list 
WHERE dealer_code = '1234'
AND sales_id != '.D999'
AND sales_entry_date BETWEEN '2013-11-01 00:00:00' AND '2014-11-31 23:59:59'";

Upvotes: 1

Views: 39

Answers (4)

Sadikhasan
Sadikhasan

Reputation: 18600

$total_query = "SELECT COUNT(sales_points) AS total_sales_points 
FROM sales_list 
WHERE dealer_code = '1234'
AND sales_id NOT LIKE '%.D999'
AND sales_entry_date BETWEEN '2013-11-01 00:00:00' AND '2014-11-31 23:59:59'";

Upvotes: 0

Nps
Nps

Reputation: 1658

I think query should be as below.

  $total_query = "SELECT COUNT(sales_points) AS total_sales_points 
    FROM sales_list 
    WHERE dealer_code = '1234'
    AND sales_id NOT LIKE '%.D999%'
    AND sales_entry_date BETWEEN '2013-11-01 00:00:00' AND '2014-11-31 23:59:59'";

Upvotes: 0

mparafiniuk
mparafiniuk

Reputation: 320

SELECT COUNT(sales_points) AS total_sales_points 
FROM sales_list 
WHERE dealer_code = '1234'
AND sales_id NOT LIKE '%.D999%'
AND sales_entry_date BETWEEN '2013-11-01 00:00:00' AND '2014-11-31 23:59:59'";

Upvotes: 0

prava
prava

Reputation: 3986

Try this : 

$total_query = "SELECT COUNT(sales_points) AS total_sales_points 
                FROM sales_list 
                WHERE dealer_code = '1234'
                AND sales_id NOT LIKE '%.D999'
                AND sales_entry_date BETWEEN '2013-11-01 00:00:00' AND '2014-11-31 23:59:59'";

Upvotes: 3

Related Questions