asked on

Mysql distict with other select items

Hi,

I have this query:

SELECT telephone, store_name, order_status_id, date_added, count(total) AS totalorders
FROM
`order` torder
WHERE
(torder.order_status_id = '2' OR torder.payment_method = 'PayPal') AND
torder.date_added >= DATE_SUB(CURDATE(),INTERVAL 0 DAY)
GROUP BY store_name

Because of the way data has been saved some rows are doubled up, the only row I can use is 'telephone' what I want is to somehow add a 'DISTINCT' on 'telephone, have tried but all rows show,.
What am I missing?

Thanks

ASKER CERTIFIED SOLUTION

stu215

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

thombeck

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

stu215

Try this:

SELECT telephone, store_name, order_status_id, date_added, count(total) AS totalorders
FROM `order` torder
WHERE telephone IN
(SELECT DISTINCT telephone
FROM `order` torder
WHERE
(torder.order_status_id = '2' OR torder.payment_method = 'PayPal') AND
torder.date_added >= DATE_SUB(CURDATE(),INTERVAL 0 DAY)
)

NOTE:: You should expect to see the same store listed more than once if it has different phone numbers going by this query...

SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

stu215

Arg, after thinking more about it the query i posted above wont work as it will pull any record that matches a telephone number from the inner query. ( sorry about that )

What kind of results are you trying to get from this?

Is cleaning up the data in the table a possibility ?

Tony Pearce

ASKER

Thanks everyone, do I need to rethink this whole thing?

stu215

It seems as though you might consider fixing your tables so that you have a better structure...
( will make your reporting easier in the future )

Assuming your dealing with orders:
-- Maybe have a table with header records that holds the transaction store info, date etc...

-- and a seperate table for line items that are linked to the transaction header record ID
that would contain item info, qty, price, discounts which you could query and sum up easily

Tony Pearce

ASKER

Yes agree. I am running this against an e-commerce system, so cannot make changes, perhaps I should run a make table to tidy data before, then run the above query against that.

Thanks again everyone

Guy Hengel [angelIII / a3]

maybe post the sample data of what you have in the table, and what you want as output from that sample data.
it may help us to understand what you really want.

Tony Pearce

ASKER

Took my own advise! built a temp table to run query and everything is fine.

Thanks to all of you for assistance.