sqlite add tag
Anonymous 1739
I have a question about this fiddle:

<>https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=efd45caa7d0fdd02522662d98226cadd

 I have a SQLite DB with five tables and corresponding columns:

    Tab1 = {Job_ID, Company_ID, Source_ID}
    Tab1_Category = {JOb_ID, CAtegory_ID}
    Category = {ID, First_level, Second_level}
    Tab2 = {Job_ID, Log_Date, Clicks, Applications}
    Source = {ID, Name}
    
I need the following results with one query>

* list of all JOB_ID (Tab1) and Company_ID (Tab1) where First_level (from table category) is "ggg" or "sss" and Name (from table Source) is "mmm"
* sum of clicks and sum of applications (Tab2) per Job_ID
* sum of distinct Second_level (from table Category)
* sum of applications for each company_ID (a company_ID can have many Job_ids)

This is what I did so far, but is not working the way i want it>
```
SELECT t1.job_id, t1.company_id,
SUM(t2.clicks), SUM(t2.applications), COUNT(DISTINCT c.second_level)
FROM TAB1 t1
JOIN SOURCE s ON s.id = t1.source_id
JOIN TAB1_CATEGORY tc ON t1.job_id = tc.job_id 
JOIN CATEGORY c ON tc.category_id = c.id
JOIN TAB2 t2 ON t1.job_id = t2.job_id
WHERE c.first_level IN ('ggg', 'sss') AND s.NAME ='mmm'
GROUP BY t1.job_id
```

What I get is sum of all clicks/applications and not per job_id. :

| job_id | company_id | SUM(t2.clicks) | SUM(t2.applications) | COUNT(DISTINCT c.second_level) |
| ------ | ---------- | ------ | ------------ | ------------------- | ---------------------- |
| 3 | 222 | 18 | 0 | 2 |
| 4 | 222 | 18 | 8 | 2 |

And this is what i want to get:

| job_id | company_id | SUM(t2.clicks) | SUM(t2.applications) | COUNT(DISTINCT c.second_level) | Total Appl per company |
| ------ | ---------- | ------ | ------------ | ------------------- | ---------------------- |
| 3 | 222 | 9 | 0 | 2 | 4 |
| 4 | 222 | 9 | 4 | 2 | 4 |

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.