I have GENDER and Marital_status Column store data in integer value

Gender:

1. Male
2. Female

Marital Status:

1. Single
2. Widow
3. Married

I want to show a report how many Male and Female are in this table. also same as Marital Status.. i need a single query for this reporting.

Like, Count(Male) =100 and Count(female)=140 this.

Like, Count(married) =120, Count(single)=100 and Count(winddow)=10 this.

Please give me the Mysql/PHP solution.

Column Data Detail..

| Gender | Marital Status |

1               2
1               3
2               2
2       开发者_如何学Go        1

Thanks,

---

A common trick is to use SUM instead of COUNT, and put an expression inside the SUM that is 1 on rows you want to count, 0 otherwise. The SUM of 1's is equal to the COUNT of them.

SELECT
  SUM(Gender=1) AS Male_Count,
  SUM(Gender=2) AS Female_Count,
  SUM(Marital_status=1) AS Single_Count,
  SUM(Marital_status=2) AS Widowed_Count,
  SUM(Marital_status=3) AS Married_Count
FROM mytable;

In MySQL, a boolean expression (such as equality comparison) returns 1 or 0. This isn't the case in standard ANSI SQL or in most other RDBMS implementations, so you'd have to write out longer CASE expressions if you want to support standards.

---

select
sum(case when gender = 1 then 1 else 0 end) as male,
sum(case when gender = 2 then 1 else 0 end) as female,
sum(case when gender = 1 and status = 1 then 1 else 0 end) as male_singles,
.... and so on
from table

---

are you about

 SELECT gender,status,count(*) FROM table GROUP BY gender,status