获取有关客户总访问量和收入的数据

Question

I have two tables, tbl_customers and tbl_transactions, where the fields of them are as follows:

**tbl_customer:**

c_id  |  c_name  |  age  |  Slab  |  gender  |  occupation  |  married  |  priority  |  ratings  |  contact  |  email  |  dob  |  anniversary

**tbl_transaction**


t_id  |  c_id  |  d_id  |  f_id  |  date_time  |  bill  |  member

The table tbl_transaction is a global table containing the transactions of all departments, differentiated by d_id.

Now I want to run a query that fetches c_id, total visits(means no of rows a c_id apppears in tbl_transaction), total revenue(means SUM(bill) of a particular c_id in) and the respective priority and ratings as it is in tbl_customer.

The query I was trying is :

Select c_id,
COUNT(cid) as tot_visit,
SUM(bill) as tot_revenue, priority, ratings 
From tbl_customer c
inner join tbl_transaction t on c.c_id=t.c_id
Where $r_id='r1' AND $c_id='".$cid."'
group by c_id

So basicaly I want to display the total visits and revenue of each customer in customer table, but not sure what is the right way to do it.

Any help would be appreciated.

This is the error I am getting in SQL Engine

Answer 1

According to your error there are fields in both tables that have same name in your tables you need to give the table alias for the field, also when you are filtering on only one group then there is no need to use group by because you are using AND c.c_id='".$cid."' for a single group

Select c.c_id,
COUNT(c.cid) as tot_visit,
SUM(t.bill) as tot_revenue, c.priority, c.ratings 
From tbl_customer c
inner join tbl_transaction t on c.c_id=t.c_id
Where $r_id='r1' AND c.c_id='".$cid."'
group by c.c_id