Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Mysql distict with other select items

Posted on 2013-01-08
10
Medium Priority
?
375 Views
Last Modified: 2013-01-09
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
0
Comment
Question by:tonypearce
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 10

Accepted Solution

by:
stu215 earned 668 total points
ID: 38756443
SELECT DISTINCT  applies to all the column names you list not just the one its next to.
http://www.w3schools.com/sql/sql_distinct.asp

You could try something similar to this ( wont work due to the group by clause ):

SELECT telephone, store_name, order_status_id, date_added, count(total) AS totalorders
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)
GROUP BY store_name
0
 
LVL 2

Assisted Solution

by:thombeck
thombeck earned 668 total points
ID: 38756451
Hi,

if you add distinct, then every row will be shown, which has different values.
The Distinct is not for one singe column.

can you please provide some sample rows (and the expected result)
0
 
LVL 10

Expert Comment

by:stu215
ID: 38756461
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...
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 664 total points
ID: 38756554
0
 
LVL 10

Expert Comment

by:stu215
ID: 38756565
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 ?
0
 

Author Comment

by:tonypearce
ID: 38756589
Thanks everyone, do I need to rethink this whole thing?
0
 
LVL 10

Expert Comment

by:stu215
ID: 38756611
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
0
 

Author Comment

by:tonypearce
ID: 38758022
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
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38758035
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.
0
 

Author Comment

by:tonypearce
ID: 38758291
Took my own advise! built a temp table to run query and everything is fine.

Thanks to all of you for assistance.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month13 days, 11 hours left to enroll

963 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question