MYSQL - Return Result of one SELECT minus another SELECT

Question

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

score 2 · Accepted Answer · edited May 23 '17 at 11:59

2

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]

edited May 23 '17 at 11:59

Community

1
1

answered Feb 27 '15 at 21:23

emrrd

36
2

Absolutely perfect. Thank you very much. – user3216557 Feb 27 '15 at 21:47

score 0 · Answer 2 · answered Feb 27 '15 at 21:29

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.

Thanks for the help. The answer it gives seems slightly different to simply running two queries separately. My Query 1 gives Jan - 103 Feb - 274. Your Query gives Jan 70 Feb 184. EDIT - Just noticed a county is missing from the IN. Ill give it another go — user3216557, Feb 27 '15 at 21:52

MYSQL - Return Result of one SELECT minus another SELECT

2 Answers2