mysql - wrong results after joining tables

36
May 09, 2018, at 07:48 AM

I have 3 tables.

1 - sales - lists sales info, only contains item codes
2 - item - item info, including item category 
3 - category - cat info

what I need in the end is to return the top seller categories. I've written a successful query for top seller items, but this one needs only the sales table.

what I have so far.

Select item.cat, sum(sales.qty) as Qty, FORMAT(sum(sales.paytot),2) 
as Total 
from sales
left join item on item.code = sales.itemcode
group by item.cat
order by sum(paytot) desc

Expectation: Return the total sales for each separate category. Results:

 cat     QTY    TOTAL
'40503','55','19,381.47'
'41118','55','19,381.47'
'41740','56','19,381.47'
'30443','55','19,381.47'
'43904','55','19,355.48'
'50421','55','19,355.48'
'44191','55','19,355.48'
'52506','55','19,355.48'
'45091','55','19,355.48'
'53115','55','19,355.48'
'48165','55','19,355.48'
'43447','55','19,355.48'
'14349','55','19,355.48'
'53921','55','19,355.48'
'49055','55','19,355.48'
'43726','55','19,355.48'
'49406','55','19,355.48'
'48154','21','6,625.53'
'43896','21','6,625.53'
'41169','21','6,625.53'
'30435','21','6,625.53'
'49068','21','6,625.53'
'44183','21','6,625.53'
'41732','21','6,625.53'
'49419','21','6,625.53'
'45083','21','6,625.53'
'53935','21','6,625.53'
'50409','21','6,625.53'
'43439','21','6,625.53'
'40530','21','6,625.53'
'52494','21','6,625.53'
'14343','21','6,625.53'
'43718','21','6,625.53'
'53103','21','6,625.53'

as you can see, it displays every category for each total. I need the total PER category. I haven't even tried to get the name of the category yet.

I'm not very familiar with mysql and haven't worked with it in a while. What am I missing here?

READ ALSO
query result taking time to load

query result taking time to load

I have a query that fetches data from Six tables but it takes too much time to fetch dataThe browser loads and shows sometimes nothing as a result

38
Importing to phpmyadmin error: Can't create table 'db.#sql-8544e_8970dd' (errno: 150)

Importing to phpmyadmin error: Can't create table 'db.#sql-8544e_8970dd' (errno: 150)

hi I am getting the below error every time i am trying to import a sql file, this table was exported from phpmyadmin on my localhost originally and I am trying to import it on the server

60
How to convert raw SQL query into Silverstripe SQLQuery abstraction layer

How to convert raw SQL query into Silverstripe SQLQuery abstraction layer

I have a page that I'm trying to pull articles related the page from a databaseI have the SQL query that pulls what I need but I keep getting the error "Unknown column 'Fashion' in 'where clause'"

75
Php: sql error “ Error: Unknown column 'RM' in 'where clause' with query”

Php: sql error “ Error: Unknown column 'RM' in 'where clause' with query”

I'm trying to create a php file that searches on a DB that I created with mysql in ubuntu, but when I run the query I get this error but I can not understand why !!! When I print the error, the data I've passed is correct and is displayed in the string...

51