Using MySQL on XAMPP. I have this table that I'm trying to produce this output with. Some of the rows do repeat so I need to return just the unique rows and the occurrences of the license in each.
| line id | red_id | cmp_id | cmp_name | cmp_version | app_id | app_name | app_vers | license | status | requester | monitor_id | monitor_dig | issue_count |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 76074884 | 77960664 | Unicode for C Sharp (Unicode4C) | 67.9 | 77956767 | LTS JSON L | 9.9 | Unicode License V7 | Approved | CZ | 76654 | na | 0 |
| 2 | 76074884 | 77960664 | Unicode for C Sharp (Unicode4C) | 67.9 | 77956767 | LTS JSON L | 9.9 | Unicode License V7 | Approved | TV | 76654 | na | 0 |
| 204 | 79895001 | 87965001 | Common Component1 | 1.2 | 95655001 | Component1 | 1.0 | Commercial License | Approved | CZ | 5260 | na | 2 |
Example of desired output
| app_name | app_version | oss_count | commercial_count | total |
|---|---|---|---|---|
| app_1_name | 1.0 | 45 | 5 | 50 |
| app_2_name | 2.0 | 34 | 0 | 34 |
oss count is the count from column license of licenses that don't contain 'commercial' in it. commercial count is the count from column licenses that contain 'commercial' in it.
I've tried the following queries which get me close to what I want. This one just repeats the overall totals in each row.
SELECT DISTINCT app_name, app_version,
(SELECT COUNT(license)
FROM apps_components
WHERE license NOT LIKE '%Commercial%') AS oss_count,
(SELECT COUNT(license)
FROM apps_components
WHERE license LIKE '%Commercial%') AS commercial_count,
(SELECT COUNT(license)
FROM apps_components) AS total
FROM apps_components
GROUP BY app_name, app_version;
and this one does count the occurrences for each unique row but it's not quite right as it repeats for the commmcercial_count column. So I think those should be 0 if one of the app_names do not contain any occurrences of 'commercial` in it.
SELECT DISTINCT app_name, app_version,
COUNT(CASE WHEN license NOT LIKE '%Commercial%' THEN 1 ELSE 0 END) as oss_count,
COUNT(CASE WHEN license LIKE '%Commercial%' THEN 1 ELSE 0 END) as commercial_count
FROM apps_components
GROUP BY app_name;
If more data is needed or examples please let me know and I can update this. Appreciate any help!