SQL Count Alias Possible?-CodePudding

I'm trying to count the number of events that happened for each customer. However, the table with the events do not have the name of the customer only the ID.

So I am replacing the ID of the customer with the name from another table, but can't figure out how to count them from there. Any help would be great.

This query will show all the events but no count.

select
(select n.company_name from company_details n where n.delete_flag = '0' and n.company_id = u.company_id) company, 
u.company_id,
l.*
from event_logging l, user_details u
where l.created_Datetime > sysdate -1
and u.user_name = l.created_by
order by l.created_datetime desc;

This query will show what I want, but wont show me the names only ID.

SELECT created_by, count(created_by) AS CountOf
FROM event_logging
where created_datetime> sysdate -1
GROUP BY created_by
order by countof desc;

Sorry, trying to clean it up. New at this. Will provide more details.

Tables

company_details N	event_logging L	user_details U
n.company_id	l.company_id	u.company_id
n.company_name	l.created_by (same as u.user_id)	u.user_name
n.delete_flag	l.created_datetime	u.user_id ( same as l.created_by)

Example Data

company_details N

n.company_id	n.company_name	n.delete_flag
1A	Donuts	0
2B	Taxi	0
3C	Hardware	0
D4	Store	0

event_logging L

l.company_id	l.created_by	l.created_datetime
1A	1	2/2/2022
2B	2	2/2/2022
3C	3	2/2/2022
D4	4	2/2/2022

user_details U

u.company_id	u.user_name	u.user_id
1A	Donuts	1
2B	Taxi	2
3C	Hardware	3
D4	Store	4

Query Results

First Top Query

Company	Company_ID	u.user_id
Donuts	1A	1
Donuts	1A	2
Donuts	1A	3
Donuts	1A	4

Second Query

Created_by	COUNTOF
1	3000
2	255
3	125
4	11

Desired Results

Company	COUNTOF
Donuts	3000
Taxi	255
Hardware	125
Store	11

CodePudding user response：

The way I understood it, it is just a join of 3 tables (with some conditions):

select e.created_by,
       c.company_name,
       count(*) as countof
from event_logging e join user_details u on u.user_name = l.created_by
                     join company_details c on c.company_id = e.company_id
where l.created_datetime > sysdate - 1
  and c.delete_flag = '0'
group by e.created_by,
         c.company_name
order by countof desc;