mysql 2 distinct values

[unwashed potato!]

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

$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

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.

 

[unwashed potato!]

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.

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.

 

[unwashed potato!]

the data looks something like the below

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

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.

 

[unwashed potato!]

Which returns -

+-------+-------+----------------+----------+-------------------+
| 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     |
+-------+-------+----------------+----------+-------------------+

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.

 

[unwashed potato!]

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.

$sql="select count(DISTINCT(postcode)) as total from quotes WHERE date = '2020-01-17'  && hide != '1'  && email != '[email protected]' GROUP BY 'postcode', 's' ";

 

[unwashed potato!]

Just add in the service to the distinct count.

$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' ";

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'];

 

[unwashed potato!]

Oh, maybe it's the brackets?

count(DISTINCT postcode, service)

 

[unwashed potato!]

thanks