Solved

Mysql distict with other select items

Posted on 2013-01-08
10
370 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 10

Accepted Solution

by:
stu215 earned 167 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 167 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 166 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

635 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