mysql 2 distinct values

unwashed potato!

unwashed potato!

unwashed potato!

Soldato
Joined
12 Feb 2006
Posts
17,380
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.
 
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:
AHarvey said:
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.
Click to expand...

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.
 
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
 
the data looks something like the below

Code: 
id            name                     email                       postcode            service                          date                 booked         total
1            aaron                  [email protected]          gu1 1px             oven cleaning              12/01/20                 0               £80
2            aaron                  [email protected]          gu1 1px             oven cleaning              12/01/20                 1               £95
3            aaron                  [email protected]          gu1 1px           builders cleaning           12/01/20                 0               £100
4            josh                    [email protected]          gu2 2aa              carpet cleaning             11/01/20                 1               £40
5            josh                    [email protected]          gu2 2aa              carpet cleaning             16/01/20                 0               £45
6            mike                   [email protected]         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:
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)
 
Fairly sure the answer is no said:
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.
Click to expand...

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 | [email protected] | GU11PX   | builders cleaning |
|     2 | Aaron | [email protected] | GU11PX   | oven cleaning     |
|     2 | Josh  | [email protected]  | GU22AA   | carpet cleaning   |
|     1 | Mike  | [email protected]  | 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;
 
visibleman said:
Which returns -
Code: 
+-------+-------+----------------+----------+-------------------+
| total | name  |     email      | postcode |      service      |
+-------+-------+----------------+----------+-------------------+
|     1 | Aaron | [email protected] | GU11PX   | builders cleaning |
|     2 | Aaron | [email protected] | GU11PX   | oven cleaning     |
|     2 | Josh  | [email protected]  | GU22AA   | carpet cleaning   |
|     1 | Mike  | [email protected]  | GU01TT   | oven cleaning     |
+-------+-------+----------------+----------+-------------------+
Click to expand...

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.
 
Fairly sure the answer is no said:
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.
Click to expand...

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).
 
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 != '[email protected]' GROUP BY 'postcode', 's' ";
 
Fairly sure the answer is no said:
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 != '[email protected]' GROUP BY 'postcode', 's' ";
Click to expand...

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 != '[email protected]' GROUP BY 'postcode','service', 's' ";
 
touch said:
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 != '[email protected]' GROUP BY 'postcode','service', 's' ";
Click to expand...
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'];
 
You must log in or register to reply here.
Back
Top Bottom