Question

I want write a MySQL query to get the number of customers who have made at least one purchase in each group (gender, education), I know how to do with two seperate query. but is that possible to get one query with the column like:

gender | counts | education | counts

The first 5 rows of each table looks like this:

customers

customer_id | first_name | last_name | state | birthdate | education | gender | date_account_opened

50 | Audrey | Osborn | CA | 1939-03-09 | Partial High School | M | 2010-06-03 |
100 | Mary | Hunt | CA | 1946-12-14 | Partial High School | F | 2007-08-06 |
150 | Mary | McCormick | WA | 1946-05-06 | High School Degree | F | 2008-11-06 |
200 | Pamela | Yates | CA | 1994-06-13 | Partial College | F | 2009-07-08 |
250 | Louis | McGrath | OR | 1936-03-25 | High School Degree | M | 2010-06-20

sale:

product_id | store_id | customer_id | promotion_id | store_sales | store_cost | units_sold | transaction_date

758 | 7 | 1150 | 0 | 12.7600 | 4.0832 | 4.0000 | 2015-01-05 |
498 | 7 | 1150 | 0 | 5.1000 | 2.2440 | 2.0000 | 2015-01-05 |
72 | 7 | 3850 | 0 | 13.7000 | 5.7540 | 5.0000 | 2015-01-10 |
942 | 7 | 3850 | 0 | 9.3600 | 4.2120 | 4.0000 | 2015-01-10 |
1258 | 7 | 3850 | 0 | 2.4400 | 1.1468 | 4.0000 | 2015-01-10

Thanks!

Answer 1

create table customers (

customer_id varchar(6) primary key,

first_name char(15),

last_name char(15),

state char(15),

birthdate date,

education varchar(40),

gender char,

date_account_opened date

);

insert into customer

values('50', 'Audrey', 'Osborn', 'CA', '1939-03-09', 'Partial High School', 'M', '2010-06-03');

insert into customer

values('100', 'Mary', 'Hunt', 'CA', '1946-12-14', 'Partial High School', 'F', '2007-08-06');

insert into customer

values('150', 'Audrey', 'McCormick', 'WA', '1946-05-06', 'High School Degree', 'F', '2008-11-06');

insert into customer

values('200', 'Pamela', 'Yates', 'CA', '1994-06-13', 'Partial College', 'F', '2009-07-08');

insert into customer

values('250', 'Louis', 'McGrath', 'OR', '1936-03-25', 'High School Degree', 'M', '2010-06-20');

create table sale (

product_id varchar(6),

store_id varchar(6),

customer_id varchar(6) references customer(customer_id),

promotion_id varchar(6),

store_sales number(2,4),

store_cost number (1,4),

units_sold number(1,4),

transaction_date date

);

insert into sale

values('758', '7', '1150', '0', 12.7600, 4.0832, 4.0000, '2015-01-05');

insert into sale

values('498', '7', '1150', '0', 5.1000, 2.2440, 2.0000, '2015-01-05');

insert into sale

values('72', '7', '3850', '0', 13.7000, 5.7540, 5.0000, '2015-01-10');

insert into sale

values('942', '7', '3850', '0', 9.3600, 4.2120, 4.0000, '2015-01-10');

insert into sale

values('1258', '7', '3850', '0', 2.4400, 1.1468, 4.0000, '2015-01-10');

select count(customer_id)

from customers, sale

where customers.customer_id=sale.customer_id AND sum(store_sales)>0;