CODEWITHSUNDEEP
mysqlsql
user3216557
user3216557

Reputation: 81

MYSQL - Return Result of one SELECT minus another SELECT

Essentially what im trying to get is the increase in views of a property on a property website after running an advertising campaign. Every view is equal to a row in the Views Table. Assuming the advertising campaign ran the month of Jan 2014.

I can run 2 seperate Queries 1) to get the Count of january 2) To get the count of February

Query 1 - Views For January

    SELECT COUNT(Views.ViewId) AS 'January Munster Views'
FROM Views
INNER JOIN Property
ON Views.PropertyId=Property.PropertyId
WHERE Views.ViewsDate LIKE '2015-01-%'
AND Property.PropertyPrice BETWEEN "800" AND "1000"
AND (Property.PropertyCounty='Co Waterford' OR Property.PropertyCounty='Co Cork' OR Property.PropertyCounty='Co Clare' 
    OR Property.PropertyCounty='Co Kerry' OR Property.PropertyCounty='Co Tipperary' OR Property.PropertyCounty='Co Limerick' );

Result = 103

Query 2 - Views For February

    SELECT COUNT(Views.ViewId) AS 'February Munster Views'
FROM Views
INNER JOIN Property
ON Views.PropertyId=Property.PropertyId
WHERE Views.ViewsDate LIKE '2015-02-%'
AND Property.PropertyPrice BETWEEN "800" AND "1000"
AND (Property.PropertyCounty='Co Waterford' OR Property.PropertyCounty='Co Cork' OR Property.PropertyCounty='Co Clare' 
    OR Property.PropertyCounty='Co Kerry' OR Property.PropertyCounty='Co Tipperary' OR Property.PropertyCounty='Co Limerick' );

Result = 274

Is there any way to just return 171 as a result with a column title of "Increase" ?

I could of course just do the work in Java or PHP but id like to know if its possible just using SQL Statement?

Thanks

Upvotes: 0

Views: 3311

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270493

Just use conditional aggregation:

SELECT SUM(CASE WHEN v.ViewsDate >= '2015-01-01' and v.ViewsDate < '2015-02-01' THEN 1 ELSE 0 END) AS Jan,
       SUM(CASE WHEN v.ViewsDate >= '2015-02-01' and v.ViewsDate < '2015-03-01' THEN 1 ELSE 0 END) AS Feb
FROM Views v INNER JOIN 
     Property p
     ON v.PropertyId = p.PropertyId
WHERE p.PropertyPrice BETWEEN 800 AND 1000 AND
      p.PropertyCounty IN ('Co Waterford', 'Co Cork', 'Co Clare', 'Co Kerry', 'Co Tipperary');

Some other tips in the query:

  • Use table aliases. They are easier to type and to read.
  • Use IN instead of OR. That is also easier to write and to read and less prone to error.
  • Don't use single quotes for column aliases. That can lead to confusion.
  • Don't use like on dates. All databases have built-in support for dates and you don't generally need to treat them as strings for comparisons.

Also, you don't mention the database you are using. There may be simpler constructs.

Finally, you can get the difference just by subtracting the first two values.

Upvotes: 0

emrrd
emrrd

Reputation: 36

If I'm understanding the question correctly, just put each query in parentheses and subtract them like so:

SELECT (query1) - (query2) AS Increase

[see https://stackoverflow.com/questions/1589070/subtraction-between-two-sql-queries]

Upvotes: 2

Related Questions