Using SELECT clause for getting customers info

[UnknownX]

Hi guys:

Hope you can answer a MySQL question I can give a solution.

I have a table like this one:

+------+----------+-----+-------+
| name | areacode | mark1| mark2 |
+------+----------+-----+-------+
| John | 1 | 1 | 0 |
| Andy | 1 | 0 | 0 |
| Bob | 2 |1 | 1 |
| Dina | 2 | 1 | 0 |
+------+----------+-----+-------+

mark1: means if this person has been visited
mark2: means if this person has buyed something from our store
areacode: is the area where this person lives (area codes are specified in another table)

So, i would like to create the next table with information:

areacode | total customers | visited | buyed something
------------------------------------------------------------------
1 | 2 | 1 | 0
2 | 2 | 2 | 1

I would like to make just one query to retrieve this information.

For example is I use something like this:
SELECT areacode, COUNT(*) FROM tbl_customers GROUP BY areacode;

I get a table with the area codes and totals of customers from each area code.

If I use:
SELECT areacode, COUNT(*) FROM tbl_customers WHERE mark1='1' GROUP BY areacode;

I get a table with area codes and totals of visited customers from each area code.

An so on...

But i would like to make just one query to get a table like shown, with all information of an area code in one row. I don't know how to make this and hope you can help me with this.

Best regards,

Andy

[si_the_geek]

As you are using the values 0 and 1, you can perform this using a simple Sum, eg:

SELECT areacode, COUNT(*), Sum(Mark1), Sum(Mark2) FROM tbl_customers GROUP BY areacode;


If you were using any other values you would need to use slightly more complex SQL, which would be specific to your DBMS (I have no idea what MySQL uses, but options in others would be CASE and IF).

[UnknownX]

Yeah, you are right! It's a good solution. Anyways, If somebody knows another way to do that, would be nice (just to learn).

Thank you si_the_geek for your great answer!

[si_the_geek]

No problem

Here's how you could do it in SQL Server:
SELECT areacode, COUNT(*), Sum(Case Mark1=1 THEN 1 ELSE 0 END), Sum(Case Mark2=1 THEN 1 ELSE 0 END) FROM ....

And in Access:
SELECT areacode, COUNT(*), Sum(If(Mark1=1, 1, 0), Sum(If(Mark2=1, 1, 0) FROM ...