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