CODEWITHSUNDEEP
sqldatabase
Steffan Harris
Steffan Harris

Reputation: 9336

Comparing if two columns are equal in Where clauses

I would like to compare if two columns are equal in the where clause. The columns are two columns are from different tables that have been joined

This is the statement that I am using

SELECT authors.state, sum(qty*price)as sales_revenue 
  from authors,titles, stores, sales 
 WHERE authors.state = (SELECT  stores.state from stores)

but I get an error that says this M

sg 8120, Level 16, State 1, Line 1
Column 'authors.state' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Upvotes: 3

Views: 6644

Answers (2)

Crimsonland
Crimsonland

Reputation: 2204

This:

SELECT a.state,
       sum(l.qty*l.price)as sales_revenue 
       FROM authors a
       LEFT JOIN stores s on a.state=s.state
       GROUP BY a.state

Specify what columns are available in each table so you can check its relationships

e.g 

authors = id,state
store = id,store

Regards

Upvotes: 0

Mark Byers
Mark Byers

Reputation: 838796

Use this:

WHERE authors.state = stores.state

Or better, use the ANSI-92 JOIN syntax. 

SELECT authors.state, sum(qty*price) as sales_revenue
FROM authors
JOIN stores ON authors.state = stores.state
JOIN titles ON ...
JOIN sales ON ...
GROUP BY authors.state

Upvotes: 4

Related Questions