CODEWITHSUNDEEP
sql
someuser2491
someuser2491

Reputation: 1968

How to simplify the SQL query with AND and OR operators to retrieve sums of particular columns?

I want to get the sum of books and pens from the item table based on comp_id and expirationdate from tables item and plan.

Below is how the tables look like:

enter image description here

I want to get the sum of books and pens for comp_id = 1; the output should be 15 for books and 30 for pens.

I have this query and it works fine

SELECT SUM(books) AS books, SUM(pens) AS pens
FROM plan 
JOIN item ON plan.id = item.plan_id 
          AND item.comp_id = '1'  
          AND (item.expiry IS NULL OR item.expiry > NOW()) ;

How can I simply the above query?

Could someone help me with this? Thanks

Upvotes: 0

Views: 54

Answers (2)

jpock76
jpock76

Reputation: 76

If you are selecting from a rather large dataset, you may get better performance by not using a function inside of your JOIN. You can declare a value for Now() at the top, and use the variable in your join. That is one way to possible increase performance. But in this tiny dataset it wont matter. You could also separate the last part of your join into a where clause. It may optimize better. 

DECLARE @date datetime
select @date = NOW()

SELECT SUM(books) AS books, SUM(pens) AS pens
FROM plan 
JOIN item ON plan.id = item.plan_id
WHERE item.comp_id = '1' AND (item.expiry IS NULL OR item.expiry > @date) ;

Upvotes: 0

Tommy
Tommy

Reputation: 366

There are a few suggestions I could make.

  1. Separate join condition from filter logic, this makes the intent of the query a bit more clear.
  2. Use table alias
  3. Use COALESCE(item.expiry, DATE '9999-12-31') which removes the OR condition.
SELECT SUM(p.books) AS books, SUM(p.pens) AS pens
FROM plan p
  JOIN item i ON i.plan_id = p.id
WHERE i.comp_id = '1'
  AND COALESCE(i.expiry, DATE '9999-12-31') > NOW();

Upvotes: 1

Related Questions