DISTINCT 2 Table

[Yohanne]

Hi Coders,

 

i have 2 tables on this scenario and now i need it to distinct the following below and to get the total of quantity. and that 3 records are came from different Branch which is i need to hide the branch name since this page is for ADMIN. 

 

any body help me and share how it to do?

 

[requinix]

I didn't follow half that question, but if you're asking about how to GROUP BY the item and get a SUM of the quantities then basically you just

SELECT i.whatever item fields, SUM(q.quantity) AS quantity
FROM whatever table is the master list of items AS i
JOIN whatever table has the quantity AS q ON matching item
GROUP BY i.identifier column

(replace in actual names for the English)

 

If you're having problems then post the schema of the table(s) involved.

[vinny42]

What do you mean by DISTINCT, because group-by does not do anything like DISTINCT, and you can't SUM on a DISTINCT without losing data.

 

 

SELECT i.whatever item fields, SUM(q.quantity) AS quantity
FROM whatever table is the master list of items AS i
JOIN whatever table has the quantity AS q ON matching item
GROUP BY i.identifier column

 

Remember  that "i.whatever itemfields" can only contain what's mentioned in the group-by part, otherwise you will get unreliable data.

Make sure to configure your MySQL to "TRADITIONAL", that prevent's MySQL from allowing you to make silly mistakes that brake your

data.

[requinix]

Remember  that "i.whatever itemfields" can only contain what's mentioned in the group-by part, otherwise you will get unreliable data.

That's one of the things that bothers me: if I'm grouping by a unique key in a table, such as the primary key, then why shouldn't I be able to access other columns from that same table? It's impossible for there to be any ambiguity about which row's data is kept in the grouping because it's guaranteed there's only one row (from that table) in each.

[vinny42]

 

it's guaranteed there's only one row (from that table) in each.

 

 

That is true in some cases, which is why PostgreSQL has implemented a feature to allow you to do this if the planner can work out if there really can be only one value for the other columns.

 

I'm not sure if that's a good thing or not, it makes life marginally easier while creating a query, but it means you must remember that the grouping is done implicitly.

 

Anyway, MySQL by default allows you to do whatever you like with your group-by statements, even the things that make no sense no matter how you look at it. So I prefer to set MySQL to CONVENTIONAL mode and accept that I have to add a few columns in group-by now and again.