Solved

Mysql distict with other select items

Posted on 2013-01-08
10
364 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 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
 
LVL 142

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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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 142

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

911 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now