1

I have performed join on 2 tables memberstable (service_id,created_date) and member_servicestable (id and user_id) the join was made on registered id and id since they are the same. I want to get the total number of user ids for service id using a join. I created a view called users and executed this query. I got what I wanted.

SELECT service_id ,COUNT(*) user_id
FROM users
WHERE created_date >= '2020-01-01 00:00:00.000' and created_date <= '2020-01-31 23:59:59.999'
GROUP BY service_id Having COUNT(*) > 1

But I need to get the same result using joins, I'm getting some errors such as grouping expressions sequence is empty, not an aggregate function.

Code to create view users:

CREATE VIEW users AS 
SELECT member_servicestable.user_id
       , memberstable.created_date
       , memberstable.service_id
       , member_servicestable.id 
FROM memberstable
     , member_servicestable 
WHERE memberstable.service_id = member_servicestable.id;
VBoka
  • 8,995
  • 3
  • 16
  • 24
Srinidhi Nagabhushan
  • 11
  • 1
  • Show us the code of the view. – VBoka Mar 06 '20 at 06:22
  • CREATE VIEW users AS SELECT member_servicestable.user_id,memberstable.created_date,memberstable.service_id,member_servicestable.id FROM memberstable,member_servicestable WHERE memberstable.service_id = member_servicestable.id; – Srinidhi Nagabhushan Mar 06 '20 at 06:41

0 Answers0