CODEWITHSUNDEEP
sqldatabasesql-server-2008
Steffan Harris
Steffan Harris

Reputation: 9326

Summing the result of a query

Ok, so I know what this query should give, it should give the sum of the total it is outputting. I am having trouble figuring out how to sum the results. The database i am using is the publishers database

Here is my query

SELECT DISTINCT authors.state, qty*price as total
from authors
JOIN titleauthor on titleauthor.au_id = authors.au_id
JOIN titles on titles.title_id = titleauthor.title_id
JOIN sales on sales.title_id = titles.title_id
JOIN stores on stores.stor_id = sales.stor_id
WHERE authors.state LIKE stores.state

and Here is the output it gives

CA 104.6500
CA 299.8000
CA 299.8500
CA 1000.0000

Upvotes: 1

Views: 97

Answers (2)

Andriy M
Andriy M

Reputation: 77657

The joins give you duplicates because they include more than one table representing a many-to-many relationship and the filtering is not sufficient to preclude the many-to-many tables from returning more than one row based on the given join conditions.

You could avoid duplicates by introducing a semi-join in the form of an EXISTS predicate and subsequent moving some of the tables there. Here's one possible way of using EXISTS in your situation:

SELECT
  stores.state,
  SUM(qty * price)
FROM sales
INNER JOIN stores ON sales.stor_id  = stores.stor_id
INNER JOIN titles ON sales.title_id = stores.title_id
WHERE EXISTS (
  SELECT *
  FROM authors a
  INNER JOIN titelauthor ta ON a.au_id = ta.au_id
  WHERE ta.titel_id = titles.title_id
    AND a.state LIKE stores.state
)
GROUP BY
  stores.state

The central table is most likely sales, it's where the figures come from. Therefore, the query is being built around sales. Other tables are joined explicitly (using a JOIN clause) as long as they return only one row for every row of sales. Once a table is going to return more than one row, it gets moved to EXISTS.

There's one more thing. While working on this query, I noticed that one join may be redundant (in your query as well as in mine). The table is titles. If your foreign keys are in order, you shouldn't need to join to titles, as titleauthor could be joined directly to stores on title_id. (Even if you don't have the corresponding foreign keys, you still don't have to include titles, as any possible non-existent titles referenced by either titleauthor or sales would be filtered out either way.)

So the final query might look like this:

SELECT
  stores.state,
  SUM(qty * price)
FROM sales
INNER JOIN stores ON sales.stor_id  = stores.stor_id
WHERE EXISTS (
  SELECT *
  FROM authors a
  INNER JOIN titelauthor ta ON a.au_id = ta.au_id
  WHERE ta.titel_id = sales.title_id
    AND a.state LIKE stores.state
)
GROUP BY
  stores.state

Upvotes: 3

juergen d
juergen d

Reputation: 204746

SELECT DISTINCT authors.state, sum(qty*price) as total
from authors
JOIN titleauthor on titleauthor.au_id = authors.au_id
JOIN titles on titles.title_id = titleauthor.title_id
JOIN sales on sales.title_id = titles.title_id
JOIN stores on stores.stor_id = sales.stor_id
WHERE authors.state = stores.state
group by authors.stat

Upvotes: 0

Related Questions