Solved

Mysql distict with other select items

Posted on 2013-01-08
10
367 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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

Suggested Solutions

Title # Comments Views Activity
Amazon Redshift 2 43
Determining creation & modification dates on MySQL tables 4 50
insert row field data graphically 4 33
MS SQL Update query with connected table data 3 59
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…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

763 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