Restricting an SQL query to certain fields in a table not used by the query

Question

I have a products table, with the fields productname, category and cost, of type varchar, varchar and double.

I then have a sales table, with the fields productname, cost, and saledate, of type varchar, double and date.

Lastly, I have a purchases table with the fields purchase, cost and purchasedate, of type varchar, double and date.

I want to calculate the number of sales for certain dates, for certain categories.

I have at the moment this query to show the monthly totals for a year:

SELECT month(saledate), SUM(cost)
FROM sales
GROUP BY month(saledate)
UNION ALL
SELECT month(purchasedate), SUM(cost)
FROM purchases
GROUP BY month(purchasedate)

How could I restrict it to certain categories?

To define certain categories from products, such as food and drink, and only include products in these categories in my calculation?

The accepted answer to the question, "Is a double really unsuitable for money?" is, "Very, very unsuitable. Use decimal." (http://stackoverflow.com/questions/316727/is-a-double-really-unsuitable-for-money) — onedaywhen, Jul 07 '10 at 07:25

score 3 · Accepted Answer · answered Jul 07 '10 at 07:06

3

SELECT month(saledate), SUM(sales.cost)
FROM sales, products
WHERE sales.productname=products.productname
AND category='food'
GROUP BY month(saledate)
UNION ALL
SELECT month(purchasedate), SUM(purchases.cost)
FROM purchases, products
WHERE purchases.productname=products.productname
AND category='food'
GROUP BY month(purchasedate)

The table needs to be part of the SQL statement in order to restrict the result-set rows based on some value held in it.

answered Jul 07 '10 at 07:06

Borealid

86,367
8
101
120

perfect...just a quick question, but to do OR in sql, would just be category='food' || 'beer' || 'smokes'...? – Jul 07 '10 at 07:29
Use the word OR. category='food' OR category='beer'. – Borealid Jul 07 '10 at 07:39
Also, note that Jun1st below is right: the categories are actually **excluded** from the resultant total when you use this query. – Borealid Jul 07 '10 at 07:40

score 1 · Answer 2 · answered Jul 07 '10 at 07:05

1

just add a WHERE clause like this one:

[...] WHERE products.category = 'food' OR prodcuts.category = 'drink' [...]

answered Jul 07 '10 at 07:05

glutorange

1,004
1
10
17

score 1 · Answer 3 · answered Jul 07 '10 at 07:37

1

If you want your calculation result doesn't include these categories, you should use WHERE.

otherwise, using HAVING

answered Jul 07 '10 at 07:37

fengd

7,385
3
36
40

score 0 · Answer 4 · answered Jul 07 '10 at 07:21

0

I'd be inclined to use a subquery rather than a join, and I think you want it in both parts of the union. But Borealid's answer is quite sound. The difference is a matter of taste.

SELECT month(saledate),
       SUM(sales.cost)
    FROM sales, products
    WHERE EXISTS (SELECT *
                  FROM products
                  WHERE purchases.productname = products.productname AND
                        category              = 'food')
    GROUP BY month(saledate)
UNION ALL
SELECT month(purchasedate),
       SUM(purchases.cost)
    FROM purchases, products
    WHERE EXISTS (SELECT *
                      FROM products
                      WHERE purchases.productname = products.productname AND
                            category              = 'food')
    GROUP BY month(purchasedate)

answered Jul 07 '10 at 07:21

Brian Hooper

20,414
23
82
132

Is there any advantage to doing a subquery, or is it only a matter of taste as you said? Also, dumb question, but how would I do an AND in SQL, to show many categories...just && ? – Jul 07 '10 at 07:40
You actually have an example of performing an AND in Mr. Hooper's answer there. And no, there's no real difference between the subqueries and the joins in this case. In general, one must watch out for explosive JOINs and inefficient/unnecessary subqueries, which are different performance demons, but here the only difference is readability. Choose whichever one you think is more readily comprehensible. – Borealid Jul 07 '10 at 07:47
Apart from joins making my head hurt, there are many different kinds of join and some subtleties involved in them. If you're at home with them, fine. With a subquery, what you see is what you get. The query optimiser will usually rewrite them for you in any case. What do you mean by 'show many categories'? If you want to include many categories in this query, you need an OR (category='food' OR category='drink'). In any case, AND is the word, not &&. – Brian Hooper Jul 07 '10 at 07:50
Thankyou both for the detailed explanations. – Jul 07 '10 at 08:36

Restricting an SQL query to certain fields in a table not used by the query

4 Answers4