SQL query for stor_name in pubs database

Question

For pubs I want to add a stor_name from the stores table into a query but its not working.

The query is:

SELECT 
    title, COALESCE(SUM(S.qty * T.price), 0) totalsale
FROM 
    titles T
LEFT JOIN
    sales S ON (S.title_id = T.title_id)
GROUP BY 
    title
ORDER BY 
    2 DESC

I need to add a stor_name from the store table. If I try and add it like this

SELECT 
    title, COALESCE(SUM(S.qty * T.price), 0) totalsale, stores.stor_name
FROM 
    titles T
LEFT JOIN
    sales S ON (S.title_id = T.title_id)
JOIN
    stores ON (S.stor_id = stores.stor_id)
GROUP BY 
    title
HAVING 
    stores.stor_name
ORDER BY 
    2 DESC

The returned result is wrong.

enter link description here

Which RDBMS is this for? Please add a tag to specify whether you're using `mysql`, `postgresql`, `sql-server`, `oracle` or `db2` - or something else entirely. — marc_s, Sep 27 '16 at 04:37
http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557 — , Sep 27 '16 at 05:51

score 0 · Answer 1 · answered Sep 26 '16 at 23:29

You need to include the name in the GROUP BY or as an argument to an aggregation function. Something like:

SELECT title, COALESCE(SUM(S.qty*T.price), 0) as totalsale, st.stor_name
FROM titles T LEFT JOIN
     sales S
     ON (S.title_id = T.title_id) JOIN
     stores st
     ON S.stor_id = st.stor_id)
GROUP BY title, st.stor_name
ORDER BY 2 desc;

Alternatively, if you just want one of many names, then:

SELECT title, COALESCE(SUM(S.qty*T.price), 0) as totalsale,
       MAX(st.stor_name)
FROM titles T LEFT JOIN
     sales S
     ON (S.title_id = T.title_id) JOIN
     stores st
     ON S.stor_id = st.stor_id)
GROUP BY title
ORDER BY 2 desc;

SQL query for stor_name in pubs database

1 Answers1