Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 377
  • Last Modified:

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
0
tonypearce
Asked:
tonypearce
  • 4
  • 3
  • 2
  • +1
3 Solutions
 
stu215Systems AnalystCommented:
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
 
thombeckCommented:
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
 
stu215Systems AnalystCommented:
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
 
stu215Systems AnalystCommented:
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
 
tonypearceAuthor Commented:
Thanks everyone, do I need to rethink this whole thing?
0
 
stu215Systems AnalystCommented:
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
 
tonypearceAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
tonypearceAuthor Commented:
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now