1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

mysql 2 distinct values

Discussion in 'HTML, Graphics & Programming' started by Fairly sure the answer is no, Jan 14, 2020 at 12:40 AM.

  1. Fairly sure the answer is no

    Sgarrista

    Joined: Feb 12, 2006

    Posts: 9,971

    Location: Surrey

    looking at the below code, is there a way to have 2 values as distinct?

    PHP:
    $sql="select count(DISTINCT(postcode)) as total from quotes WHERE origin = '$thisSite' ";
    i want to be able to say group postcode and the service they requested and count those, then group the postcode and another service we offer etc.

    was hoping the below would give me what i'm after but it doesn't seem to work. googling this doesn't give me much help either

    PHP:
    DISTINCT(postcode AND service)
    the reason for this is if i don't define the service required, then it's grouping say 6 different quotes for different services as one count.
     
  2. AHarvey

    Sgarrista

    Joined: Mar 6, 2008

    Posts: 9,857

    Location: Stoke area

    Do you have postcode and service in the same table?

    What you would normally is something like:

    SELECT SERVICE, COUNT(DISTINCT(POSTCODE)) AS TOTAL
    FROM QUOTES
    GROUP BY SERVICE


    This will give you the service in the first column and the count in the second.

    You can then play around with WHERE

    You can also use GROUP BY POSTCODE, SERVICE for multiple groups

    EDIT, it not the easiest when you can't see the database/table. I find it easier having access ot the data to play around with the code. I'd try connecting to the database directly and then using code to pull the data you want.
     
    Last edited: Jan 14, 2020 at 9:26 AM
  3. Fairly sure the answer is no

    Sgarrista

    Joined: Feb 12, 2006

    Posts: 9,971

    Location: Surrey

    i've tried that but unfortunately i get nothing back.

    this is all on one table. all i'm looking to do is count how many unique quotes we get. as users often get 3/4 quotes for the same job, i thought to group the postcode to ensure that the repeated quotes aren't throwing off the stats, but as they can also sometimes get a quote for oven cleaning, and then carpet cleaning, i'd want these to be not grouped together.
     
  4. AHarvey

    Sgarrista

    Joined: Mar 6, 2008

    Posts: 9,857

    Location: Stoke area

    Without seeing the actual data I am not sure.

    We use an ADDRESS table which contains TYPE_OF_ADDRESS which has 4 distinct types and then POSTAL_CODES

    So, if I look at grouping by TYPE_OF_ADDRESS (which would be your SERVICE) using either of these two:

    SELECT TYPE_OF_ADDRESS, COUNT(DISTINCT(POSTAL_CODE)) AS TOTAL
    FROM ADDRESS
    GROUP BY TYPE_OF_ADDRESS

    SELECT TYPE_OF_ADDRESS, POSTAL_CODE
    FROM ADDRESS
    GROUP BY TYPE_OF_ADDRESS, POSTAL_CODE
    ORDER BY TYPE_OF_ADDRESS, POSTAL_CODE

    They both bring back 9820 rows. The first brings back unique post codes per type and the second lists type / unique postcode on individual rows.

    That is down from 21514 rows in total so the code definitely works as it should.

    That's TSQL on Miicrosoft SQL Server, I can't see anything that wouldn't work on another version of sql/server
     
  5. Fairly sure the answer is no

    Sgarrista

    Joined: Feb 12, 2006

    Posts: 9,971

    Location: Surrey

    the data looks something like the below

    Code:
    id            name                     email                       postcode            service                          date                 booked         total
    1            aaron                  aaron@mail.com          gu1 1px             oven cleaning              12/01/20                 0               £80
    2            aaron                  aaron@mail.com          gu1 1px             oven cleaning              12/01/20                 1               £95
    3            aaron                  aaron@mail.com          gu1 1px           builders cleaning           12/01/20                 0               £100
    4            josh                    josh@mail.com          gu2 2aa              carpet cleaning             11/01/20                 1               £40
    5            josh                    josh@mail.com          gu2 2aa              carpet cleaning             16/01/20                 0               £45
    6            mike                   mike@mail.com         gu10 1tt             oven cleaning               12/01/20                  0               £200

    so aaron's quote should count as 2 as he's got 2 quotes for oven cleaning, and 1 for builders.
    josh should count as 1 as he's got 2 carpet cleaning,
    and mike as 1

    so the count total should be 4, however we're getting 3 as it's merging the builders and oven together.

    when i add in the group by as you've shown above, i get an error back saying

    Code:
    mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given
    i assume this is because we're no longer just counting the data but selecting the information. not sure a work around this. i don't need any of the information displayed to me other than the count of how many the query totals.
     
    Last edited: Jan 14, 2020 at 2:35 PM
  6. AHarvey

    Sgarrista

    Joined: Mar 6, 2008

    Posts: 9,857

    Location: Stoke area

    What you could try is writing the result of the select to a temp table and then doing a count(*) from that table, which would bypass the error above.

    I do all my work directly onto the database and via SP's, our web front end just calls SP's, provides any variables and the work is all done on the server.

    You may need to ask someone who can use PHP (which I think you're using)
     
  7. visibleman

    Wise Guy

    Joined: Jun 3, 2005

    Posts: 1,367

    Location: The South

    Hope this is what you're after -
    Code:
    SELECT
        COUNT(*) AS 'total',
        `TestOrders`.`name`,
        `TestOrders`.`email`,
        `TestOrders`.`postcode`,
        `TestOrders`.`service`
    FROM `TestOrders`
    WHERE 1
    GROUP BY `TestOrders`.`email`, `TestOrders`.`service`

    Which returns -
    Code:
    +-------+-------+----------------+----------+-------------------+
    | total | name  |     email      | postcode |      service      |
    +-------+-------+----------------+----------+-------------------+
    |     1 | Aaron | aaron@mail.com | GU11PX   | builders cleaning |
    |     2 | Aaron | aaron@mail.com | GU11PX   | oven cleaning     |
    |     2 | Josh  | josh@mail.com  | GU22AA   | carpet cleaning   |
    |     1 | Mike  | mike@mail.com  | GU01TT   | oven cleaning     |
    +-------+-------+----------------+----------+-------------------+

    Reason this works is that you're grouping on a UNIQUE identifier, in this case the email address (usually a postcode or name isn't unique enough; arguably an email address isn't either but it's all we've got to play with) and then the service column.
    Obviously you can do further filtering by adding conditions, essentially 'WHERE 1' queries all row data (this isn't needed), and adjust which columns your selecting etc.

    Table structure i used for testing; far from perfect in terms of field types used etc -
    Code:
    CREATE TABLE `TestOrders` (
      `id` smallint(4) UNSIGNED NOT NULL,
      `name` varchar(1024) NOT NULL,
      `email` varchar(320) NOT NULL,
      `postcode` varchar(7) NOT NULL,
      `service` varchar(1024) NOT NULL,
      `date` date NOT NULL,
      `booked` tinyint(1) UNSIGNED NOT NULL DEFAULT '0',
      `total` decimal(13,2) NOT NULL DEFAULT '0.00'
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
     
  8. Fairly sure the answer is no

    Sgarrista

    Joined: Feb 12, 2006

    Posts: 9,971

    Location: Surrey

    thanks,

    is there any way to just get the total count of the above? i'd just want the number 4 to be returned. i don't need the name, email, postcode, service etc returned to me.
     
  9. visibleman

    Wise Guy

    Joined: Jun 3, 2005

    Posts: 1,367

    Location: The South

    Remove those columns from the SELECT portion of the query and keep the COUNT(*).

    Edit - Personally i'd also remove the column alias and within PHP just return the first (and only) column when grabbing the results (or looping through etc).
     
  10. Fairly sure the answer is no

    Sgarrista

    Joined: Feb 12, 2006

    Posts: 9,971

    Location: Surrey

    unfortunately it's not working. they are still being grouped together as one.

    i've just done a test and put the same name, email post code and got a quote for ovens, i then see the counter go up, i then get a quote for carpet cleaning, and the counter doesn't budge. all the same details but the service is different.

    this is the sql line i have.

    PHP:
    $sql="select count(DISTINCT(postcode)) as total from quotes WHERE date = '2020-01-17'  && hide != '1'  && email != 'myemail@address.co.uk' GROUP BY 'postcode', 's' ";
     
  11. AHarvey

    Sgarrista

    Joined: Mar 6, 2008

    Posts: 9,857

    Location: Stoke area

    What happens if you try this:


    SELECT TYPE_OF_ADDRESS, POSTAL_CODE
    INTO #TEMP
    FROM ADDRESS
    GROUP BY TYPE_OF_ADDRESS, POSTAL_CODE

    SELECT COUNT(*) FROM #TEMP
     
  12. touch

    Capodecina

    Joined: Oct 28, 2006

    Posts: 11,223

    Location: Sufferlandria

    Just add in the service to the distinct count.

    PHP:
    $sql="select count(DISTINCT(postcode, service)) as total from quotes WHERE date = '2020-01-17'  && hide != '1'  && email != 'myemail@address.co.uk' GROUP BY 'postcode','service', 's' ";
     
  13. Fairly sure the answer is no

    Sgarrista

    Joined: Feb 12, 2006

    Posts: 9,971

    Location: Surrey

    when i do that it then returns an error.

    mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, bool given in

    this is what i follow up with.

    $result=mysqli_query($conSent,$sql);
    $data=mysqli_fetch_assoc($result);

    return $data['total'];
     
  14. touch

    Capodecina

    Joined: Oct 28, 2006

    Posts: 11,223

    Location: Sufferlandria

    Oh, maybe it's the brackets?

    count(DISTINCT postcode, service)
     
  15. Fairly sure the answer is no

    Sgarrista

    Joined: Feb 12, 2006

    Posts: 9,971

    Location: Surrey

    [​IMG]
     
  16. Fairly sure the answer is no

    Sgarrista

    Joined: Feb 12, 2006

    Posts: 9,971

    Location: Surrey

    thanks :)