Separating values from single column into two columns and counting them

ProwlingEyes

Registered User.
Local time
Today, 13:06
Joined
Sep 22, 2013
Messages
12
I posted this question in "Reports" section and here I am posting the answer. Because I think it is more related to "Queries" than reports.
I have a table that has two columns "Nationality" and "Gender". I wanted to run a query that will Group the nationality and then split the gender column into two columns and after that it will count both gender columns for each nationality. When I posted this question in "Reports" section I got the suggestion to use the SELECT COUNT in SQL. It worked but only for nationality. I couldn't get it work for the Gender column. I searched alot and the only thing I got was the SQL function to split data from one column into two but that also didn't serve the purpose (check the link to see why: goo.gl/Y3FYTj)
At last, I went on to create a cross tab query. Selected Nationality as Row header, Gender as Column header and Customer ID as calculation point. And there I got the result I needed. The SQL Code looks like this:
Code:
TRANSFORM Count(Register.[Customer ID]) AS [CountOfCustomer ID]
SELECT Register.National, Count(Register.[Customer ID]) AS [Total Of Customer ID]
FROM Register
GROUP BY Register.National
PIVOT Register.P_Gender;
Crosstab query did the trick..
 
Crosstab is another way to go. Again, this can be a simple SELECT Aggregate Query, which I have explained in the other THREAD. If you are happy with crosstab, by all mean go ahead with it. :) Just an alternative.
 

Users who are viewing this thread

Back
Top Bottom