CODEWITHSUNDEEP
sql
Steve Stretch
Steve Stretch

Reputation: 23

Another simple query with a SQL Statement (from someone just learning SQL)

Really sorry to ask all you experts what I'm doing wrong with this SQL Statement.

All I'm trying to do is list the Sales Person from the SalesExec Table and show the number of new Cars from the New Table and the number of Used Cars from the Used Table that that Salesman has sold.

SELECT SALESEXEC.SALESEXECNAME 
COUNT(NewSALES.CHASSIS) AS newCOUNT
COUNT(UsedSales.CHASSIS) AS usedCOUNT
FROM SALESEXEC.csv SALESEXEC, NewSALES.csv NewSALES, UsedSALES.csv UsedSALES
WHERE SALESEXEC.ID = NewSALES.SALESEXECID And SALESEXEC.ID = UsedSALES.SALESEXECID
GROUP BY SALESEXEC.SALESEXECNAME

This statement seems to return the same number for both the newCOUNT and usedCOUNT and I don't even know where that number has come from?

Again, I really appreciate any help on this. THanks in advance

Steve

Upvotes: 1

Views: 37

Answers (1)

T I
T I

Reputation: 9933

SELECT SALESEXEC.SALESEXECNAME, newCOUNT, usedCOUNT
FROM SALESEXEC.csv SALESEXEC
JOIN (
    SELECT SALESEXECID, COUNT(CHASSIS) AS newCOUNT
    FROM NewSALES.csv
    GROUP BY SALESEXECID
) NewSALES ON SALESEXEC.ID = NewSALES.SALESEXECID
JOIN (
    SELECT SALESEXECID, COUNT(CHASSIS) AS usedCOUNT
    FROM UsedSALES.csv 
    GROUP BY SALESEXECID
) UsedSALES ON SALESEXEC.ID = UsedSALES.SALESEXECID

Upvotes: 1

Related Questions