I need a filter where the input value is similar to any value in an aggregate, using ilike and wildcards. My attempt at a query so far is:

SELECT p.id, p.name, array_agg(vo.name)
FROM product p
LEFT JOIN product_variation pv
ON p.id = pv.product_id
LEFT JOIN variation_option vo
ON pv.variation_option_id = vo.id
GROUP BY p.id
HAVING $1 ilike any(array_agg('%' || vo.name || '%'));

But it doesn't work. It gets even more complicated: ideally, I'd be able to input an array of strings, where any of them are similar to the aggregate. Any tips? I realize this kind of works, just not as I need it to. The input value should be able to be a partial match for any of the array elements, but it is the other way around. The wild card would need to be in the input value.

I'm looking for an aggregate function supported by Oracle SQL to collect values into arrays, something similar to PostgreSQL

. I already use

for strings, I use

()

with

()

and

()

but it requires to declare a table type. Is there a more straightforward way of emulating

with Oracle SQL?

Are there any SQL-standard or DBMS implementation that offers "sample aggregator" for predictably repeated columns? I will explain, see bold text at the end. PS: this question is about "little performance gain" in big tables, and about "semantic enhancements" in the *SQL language*, in general. ---- As typical table, we can imagine a SQL-view v1 of some piece of data: Organization | City | Country | Info1 | Info2 --- | --- | --- | --- | --- LocalOrg1 | San Francisco | US | 10 | 23 LocalOrg1 | San Francisco | US | 2 | 24 Armarinhos Fer | São Paulo | BR | 11 | 55 Armarinhos Fer | São Paulo | BR | 12 | 56 My (programmer's) prediction is that all organizations of v1 exists in only one city (eg. there are only one "San Francisco City Hall"). So,on basis of my prediction, the query SELECT organization, city, country, sum(info1) as tot1, avg(info2) as avg2, count(*) as n FROM v1 GROUP BY organization, city, country -- city,country predicted repeat have some redundancy and some semantic limitation: the grouping criteria is organization, there is no need to spend CPU checking city and country. And I can say "Hey John look that query grouped by organization", because it does not make sense to say to another human "organization, city and country" when he have the same prediction in mind. It was a SQL obligation but is not the semantic essence of the query. Of course, the semantic may be better with GROUP by organization, 2,3 syntax sugar, or using max(), SELECT organization, max(city) as city, max(country) as country, sum(info1) as tot1, avg(info2) as avg2, count(*) as n FROM v1 GROUP BY organization -- better semantic, worse performance but max() lost time "comparing nothing" (because as I predicted there are only repeated things), and perhaps the query cost will be bigger than the first query. Some DBMS also offer first() and last() which might be more efficient (!) then max(), and I guess that this is the simplest solution today: use some kind of first() function optimize performance and semantic, in basis of my prediction that city and country repeats when grouping by organization. But no matter if the sample of a repeated column comes from first or last sampled row. The first/last can be also an internal optimization choice, so, the real need in this context is a kind of aggsample() function: SELECT organization, aggsample(city), aggsample(country) -- better semantic and perfornace sum(info1) as tot1, avg(info2) as avg2, count(*) as n FROM v1 GROUP BY organization -- better performance So, putting in this detailed context: **Are there are any *SQL language variation* where this kind of function (aggsample) was defined?** ----- EDITED after comments and some homework... ## NOTES Candidates and limitations: * [PostgreSQL's DISTINCT ON](http://www.postgresql.org/docs/current/static/sql-select.html#SQL-DISTINCT) . Seems to address the similar problem, but not solves the use in usual GROUP BY summarizations. * [MySQL's ANY_VALUE](https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html) , as suggested by @AndriyM. Seems perfect (!), but I never used... Will try later, install mysql and do some tests. Similar discussions: * https://stackoverflow.com/a/8373384/287948 * https://stackoverflow.com/q/36134657/287948 * ... https://stackoverflow.com/a/20347763/287948 ### Trying CREATE TABLE v1 ( Organization text, City text, Country text, Info1 int, Info2 int ); INSERT INTO v1 VALUES ('LocalOrg1', 'San Francisco', ' US', '10 ', '23'), ('LocalOrg1', 'San Francisco', ' US', '2 ', '24'), ('Armarinhos Fer', 'São Paulo', ' BR', '11 ', '55'), ('Armarinhos Fer', 'São Paulo', ' BR', '12 ', '56'); Extrange, PostgreSQL offer as valid syntax "DISTINCT ON + GROUP BY", SELECT DISTINCT ON (organization) organization, city, country, sum(info1) as tot1, avg(info2) as avg2, count(*) as n FROM v1 GROUP BY organization, city, country but not works better (worst performance) and is not valid the reduction ou grouping with only GROUP BY organization. ... no other test. PS: the only valid syntax (and result) with DISTINCT ON of postgresql is SELECT DISTINCT ON (organization) organization, City, Country, sum(Info1) OVER w AS tot1, avg(info2) OVER w AS avg2, count(*) OVER w as n FROM v1 WINDOW w AS (PARTITION BY organization); as we see, ugly syntax, and with worst performance.

I have many entities like: user

id | name
-----------
 1 | Joe
 2 | David
 3 | Jane

cars

id| name 
------------
1 | cars1 
2 | cars2 
3 | cars3 
4 | cars4 
5 | cars5 
6 | cars6 
7 | cars7 
8 | cars8 
9 | cars9

cars_data

id | price  | category | uid | car_id
---+--------+----------+-----+-------
1  | 225.00 |	p1 	   |  1  |	1
2  | 451.00 |	p2 	   |  1  |	1
3  | 324.00 |	p2 	   |  1  |	2
4  | 784.00 |	p2 	   |  1  |	3
5  | 724.00 |	p3 	   |  1  |	2
6  | 214.00 |	p1 	   |  2  |	1
7  | 451.00 |	p1 	   |  2  |	2
8  | 926.00 |	p1 	   |  2  |	3
9  | 271.00 |	p2 	   |  2  |	3
10 | 421.00 |	p2 	   |  2  |	4
11 | 684.00 |	p2 	   |  2  |	2
12 | 124.00 |	p3 	   |  2  |	5
13 | 128.00 |	p3 	   |  2  |	1
14 | 741.00 |	p1 	   |  3  |	1
15 | 965.00 |	p1 	   |  3  |	3
16 | 124.00 |	p2 	   |  3  |	4
17 | 415.00 |	p2 	   |  3  |	1
18 | 51.00 	|   p2    |  3  |	2
19 | 965.00 |	p2 	   |  3  |	6

filters

id 	| name 	  |  filter 	     | uid
----+ --------+------------------+-----
1 	| filter1 |	string filters 1 |	1
2 	| filter2 |	string filters 2 |	1
3 	| filter3 |	string filters 3 |	1
4 	| filter3 |	string filters 3 |	1
5 	| filter3 |	string filters 3 |	1
6 	| filter3 |	string filters 3 |	1
7 	| filter3 |	string filters 3 |	1
8 	| filter  |	string filters 1 |	2
9 	| filter5 |	string filters 5 |	2
10 	| filter6 |	string filters 6 |	2
11 	| filter6 |	string filters 6 |	2
12 	| filter6 |	string filters 6 |	2
13 	| filter6 |	string filters 6 |	2
14 	| filter7 |	string filters 7 |	3
15 	| filter8 |	string filters 8 |	3
16 	| filter8 |	string filters 8 |	3
17 	| filter8 |	string filters 8 |	3
18 	| filter8 |	string filters 8 |	3
19 	| filter9 |	string filters 9 |	3

assign_filters

uid | category  | filter_id
----+ ----------+-----------
1   |	p1 	    |1          
1   |	p2 	    |1          
1   |	p2 	    |2          
1   |	p2 	    |3          
1   |	p3 	    |4          
2   |	p1 	    |9          
2   |	p1 	    |8          
2   |	p1 	    |13         
3   |	p2 	    |14         
3   |	p2 	    |16         
3   |	p2 	    |17         
3   |	p3 	    |19         
3   |	p3 	    |18         
3   |	p1 	    |14         
3   |	p1 	    |18

What I want is results like this:

uid | category  | filter_id |car_id  
----+ ----------+-----------+--------
1   |	p1 	    |1          |[1] 
1   |	p2 	    |1          |[1,2,3]
1   |	p2 	    |2          |[1,2,3]
1   |	p2 	    |3          |[1,2,3]
1   |	p3 	    |4          |
2   |	p1 	    |9          |[1,2,3]
2   |	p1 	    |8          |[1,2,3]
2   |	p1 	    |13         |[1,2,3]
3   |	p2 	    |14         |[1,2,3,4,6]
3   |	p2 	    |16         |[1,2,3,4,6]
3   |	p2 	    |17         |[1,2,3,4,6]
3   |	p3 	    |19         |[6,7]
3   |	p3 	    |18         |[6,7]
3   |	p1 	    |14         |[1] 
3   |	p1 	    |18         |[1] 

How can I change the original SQL query to give me the result above? dbfiddle **update:** - Each user can define a filter for himself and each filter is specific to one user. - Each user can place cars in specific categories in the cars_data table.(categories is [p1,p2,p3]) - Each user can assign a number of filters to each of their categories. I need to know which filters each user has used, and on which cars the filter has been applied. For example, user number 1 has placed cars1, cars2, cars3 in category p1. Also, filter1, filter2, filter3 have been assigned to this category. The result I need is this:

uid | category  | filter_id |car_id  
----+ ----------+-----------+--------
1   |	p2 	    |1          |[1,2,3]
1   |	p2 	    |2          |[1,2,3]
1   |	p2 	    |3          |[1,2,3]

I've been trying to understand the MongoDB Aggregate process so I can better optimize my queries and I'm confused by usage and $match and $sort together. Sample DB has only one collection people [{ "name": "Joe Smith", "age": 40, "admin": false }, { "name": "Jen Ford", "age": 45, "admin": true }, { "name": "Steve Nash", "age": 45, "admin": true }, { "name": "Ben Simmons", "age": 45, "admin": true }] I've multiplied this data x1000 just as a POC. The DB above has one index name_1 The Following query db.people.find({"name": "Jen Ford"}).sort({"_id": -1}).explain() Has the following output { queryPlanner: { plannerVersion: 1, namespace: 'db.people', indexFilterSet: false, parsedQuery: { name: { '$eq': 'Jen Ford' } }, queryHash: '3AE4BDA3', planCacheKey: '2A9CC473', winningPlan: { stage: 'SORT', sortPattern: { _id: -1 }, inputStage: { stage: 'SORT_KEY_GENERATOR', inputStage: { stage: 'FETCH', inputStage: { stage: 'IXSCAN', keyPattern: { name: 1 }, indexName: 'name_1', isMultiKey: false, multiKeyPaths: { name: [] }, isUnique: false, isSparse: false, isPartial: false, indexVersion: 2, direction: 'forward', indexBounds: { name: [ '["Jen Ford", "Jen Ford"]' ] } } } } }, rejectedPlans: [ { stage: 'FETCH', filter: { name: { '$eq': 'Jen Ford' } }, inputStage: { stage: 'IXSCAN', keyPattern: { _id: 1 }, indexName: '_id_', isMultiKey: false, multiKeyPaths: { _id: [] }, isUnique: true, isSparse: false, isPartial: false, indexVersion: 2, direction: 'backward', indexBounds: { _id: [ '[MaxKey, MinKey]' ] } } } ] }, serverInfo: { host: '373ea645996b', port: 27017, version: '4.2.0', gitVersion: 'a4b751dcf51dd249c5865812b390cfd1c0129c30' }, ok: 1 } This makes total sense. **However** The following query results in the same set but uses the aggregate pipeline db.people.aggregate([ { $match: { $and: [{ name: "Jen Ford" }]}}, { $sort: {"_id": -1}}], {"explain": true}) Has the following output. { queryPlanner: { plannerVersion: 1, namespace: 'db.people', indexFilterSet: false, parsedQuery: { name: { '$eq': 'Jen Ford' } }, queryHash: '3AE4BDA3', planCacheKey: '2A9CC473', optimizedPipeline: true, winningPlan: { stage: 'FETCH', filter: { name: { '$eq': 'Jen Ford' } }, inputStage: { stage: 'IXSCAN', keyPattern: { _id: 1 }, indexName: '_id_', isMultiKey: false, multiKeyPaths: { _id: [] }, isUnique: true, isSparse: false, isPartial: false, indexVersion: 2, direction: 'backward', indexBounds: { _id: [ '[MaxKey, MinKey]' ] } } }, rejectedPlans: [] }, serverInfo: { host: '373ea645996b', port: 27017, version: '4.2.0', gitVersion: 'a4b751dcf51dd249c5865812b390cfd1c0129c30' }, ok: 1 } **Notice how the Aggregate Query is unable to recognize it should utilize the name index against the $match. This has massive implications as the size of the collection grows** I've seen this behavior now in Mongo 3.4, 3.6, and 4.2. https://docs.mongodb.com/v4.2/core/aggregation-pipeline-optimization/ provides this blurb > $sort + $match Sequence Optimization: When you have a sequence with $sort followed by a $match, the $match moves before the $sort to minimize the number of objects to sort. From all this, I think I'm fundamentally misunderstanding something with the Mongo aggregate command. I already understand that if I create a composite index name,_id then it will work as it includes the fields used in my $match and my $sort clause. But **why** must an index include a field from the $sort clause to be utilized to restrict my $match set? It seems obvious that we would prefer to $sort on the smallest set possible?

I was reading [this](https://learn.microsoft.com/en-us/archive/blogs/craigfr/scalar-subqueries) And I did the following:

go
create table fiirst (
col1 int,
col2 int
);
create table seecond(
col1 int,
col2 int
);
with
n1(c) as (select 0 union all select 0 ),
n2(c) as ( select 0 from n1 as t1 cross join n1 as t2),
n3(c) as ( select 0 from n2 as t1 cross join n2 as t2),
n4(c) as (select 0 from n3 as t1 cross join n3 as t2),
ids(id) as (select ROW_NUMBER() over (order by (select null)) from n4)
insert into fiirst(col1,col2)
select id,id 
from ids;
with
n1(c) as (select 0 union all select 0 ),
n2(c) as ( select 0 from n1 as t1 cross join n1 as t2),
n3(c) as ( select 0 from n2 as t1 cross join n2 as t2),
n4(c) as (select 0 from n3 as t1 cross join n3 as t2),
ids(id) as (select ROW_NUMBER() over (order by (select null)) from n4)
insert into seecond(col1,col2)
select id,id
from ids;
----Craig Freedman's query

select *
from fiirst
where fiirst.col1 > (
    select min(seecond.col1)
    from seecond
    where seecond.col2 < fiirst.col2
);

And I got an [index spool](https://www.brentozar.com/pastetheplan/?id=QpaT80Kj8T) , even though the table is a heap. The question is, how did this happen? Why do I get an index spool on a heap? In the example mentioned in the above link, there was no rows, so no spools, but here I see them?

In Athena, I want to calculate a rolling sum, over a window, that can't go below 0. For example, if summing up a column that has values

(1, 2, -1, -2, -1, -2, 1, 2)

I should get

(1, 3, 2, 0, 0, 0, 1, 3)

. Without the floor constraint, it's easy -

(X) OVER (PARTITION BY some_group ORDER BY ordering_col)

. Or if we just wanted to clip all the values at 0 after doing an actual cumulative sum. But I cannot figure out how to use window functions to achieve my desired result in general, though I can make it work for some special cases. Is this even possible?

I have two different queries that I need to UNION together and then aggregate each rate type group together into a final table. For each rate group (Rate_001mo, Rate_003mo, etc.) SELECT EffectiveDate, SUM(CASE WHEN RateName = 'FHLBCSOFR006M' THEN InterestRate ELSE 0 END) / 100.0 AS Rate_001mo, SUM(CASE WHEN RateName = 'FHLBCSOFR006M' THEN InterestRate ELSE 0 END) / 100.0 AS Rate_003mo, SUM(CASE WHEN RateName = 'FHLBCSOFR006M' THEN InterestRate ELSE 0 END) / 100.0 AS Rate_006mo, SUM(CASE WHEN RateName = 'FHLBCSOFR012M' THEN InterestRate ELSE 0 END) / 100.0 AS Rate_012mo, SUM(CASE WHEN RateName = 'FHLBCSOFR024M' THEN InterestRate ELSE 0 END) / 100.0 AS Rate_024mo, SUM(CASE WHEN RateName = 'FHLBCSOFR036M' THEN InterestRate ELSE 0 END) / 100.0 AS Rate_036mo FROM dbo.STAGING_FhlbRates WHERE EffectiveDate = '20250131' group by EffectiveDate UNION SELECT EffectiveDate, SUM(CASE WHEN RateName = 'USOSFRA' THEN InterestRate ELSE 0 END) / 100.0 AS Rate_001mo, SUM(CASE WHEN RateName = 'USOSFRC' THEN InterestRate ELSE 0 END) / 100.0 AS Rate_003mo, SUM(CASE WHEN RateName = 'USOSFRF' THEN InterestRate ELSE 0 END) / 100.0 AS Rate_006mo, SUM(CASE WHEN RateName = 'USOSFR1' THEN InterestRate ELSE 0 END) / 100.0 AS Rate_012mo, SUM(CASE WHEN RateName = 'USOSFR2' THEN InterestRate ELSE 0 END) / 100.0 AS Rate_024mo, SUM(CASE WHEN RateName = 'USOSFR3' THEN InterestRate ELSE 0 END) / 100.0 AS Rate_036mo FROM dbo.STAGING_SofrOisRates WHERE EffectiveDate = '20250131' GROUP BY EffectiveDate The columns for the final results table should include Effective Date and then each rate group (Rate_001mo, Rate_003mo, etc.)

My query with $and, $or operators is performing poorly. Even though I've built a compound index on all three fields that are specified in my query criteria, the query does a complete collection scan. I have tried both simple find and aggregation and the Winning plan for both is COLLSCAN. ### Simple find Query:

db.test.find({ $or: [ {id1: "900004"}, {relatedid: "900004"}], $and: [ { isValid: "Y" } ] } )

Execution stats:

{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "XXX",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "$and" : [ 
                {
                    "$or" : [ 
                        {
                            "Id1" : {
                                "$eq" : "900004"
                            }
                        }, 
                        {
                            "relatedID" : {
                                "$eq" : "900004"
                            }
                        }
                    ]
                }, 
                {
                    "isValid" : {
                        "$eq" : "Y"
                    }
                }
            ]
        },
        "winningPlan" : {
            "stage" : "COLLSCAN",
            "filter" : {
                "$and" : [ 
                    {
                        "$or" : [ 
                            {
                                "Id1" : {
                                    "$eq" : "900004"
                                }
                            }, 
                            {
                                "relatedID" : {
                                    "$eq" : "900004"
                                }
                            }
                        ]
                    }, 
                    {
                        "isValid" : {
                            "$eq" : "Y"
                        }
                    }
                ]
            },
            "direction" : "forward"
        },
        "rejectedPlans" : []
    },
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 1,
        "executionTimeMillis" : 61,
        "totalKeysExamined" : 0,
        "totalDocsExamined" : 100006,
        "executionStages" : {
            "stage" : "COLLSCAN",
            "filter" : {
                "$and" : [ 
                    {
                        "$or" : [ 
                            {
                                "Id1" : {
                                    "$eq" : "900004"
                                }
                            }, 
                            {
                                "relatedID" : {
                                    "$eq" : "900004"
                                }
                            }
                        ]
                    }, 
                    {
                        "isValid" : {
                            "$eq" : "Y"
                        }
                    }
                ]
            },
            "nReturned" : 1,
            "executionTimeMillisEstimate" : 60,
            "works" : 100008,
            "advanced" : 1,
            "needTime" : 100006,
            "needYield" : 0,
            "saveState" : 781,
            "restoreState" : 781,
            "isEOF" : 1,
            "invalidates" : 0,
            "direction" : "forward",
            "docsExamined" : 100006
        }
    },
    "serverInfo" : {
        "host" : "XXXX",
        "port" : ,
        "version" : "4.0.9",
        "gitVersion" : "XXX"
    },
    "ok" : 1.0
}

### Aggregation Query:

db.test.aggregate( [{ "$match":{ "$and": [ {"isValid": "Y"}, { "$or": [ {"Id1": "900004"}, {"relatedID": "900004"} ]}] }} ] )

Execution Stats:

{
    "stages" : [ 
        {
            "$cursor" : {
                "query" : {
                    "$and" : [ 
                        {
                            "isValid" : "Y"
                        }, 
                        {
                            "$or" : [ 
                                {
                                    "Id1" : "900004"
                                }, 
                                {
                                    "relatedID" : "900004"
                                }
                            ]
                        }
                    ]
                },
                "queryPlanner" : {
                    "plannerVersion" : 1,
                    "namespace" : "XXXXX",
                    "indexFilterSet" : false,
                    "parsedQuery" : {
                        "$and" : [ 
                            {
                                "$or" : [ 
                                    {
                                        "Id1" : {
                                            "$eq" : "900004"
                                        }
                                    }, 
                                    {
                                        "relatedID" : {
                                            "$eq" : "900004"
                                        }
                                    }
                                ]
                            }, 
                            {
                                "isValid" : {
                                    "$eq" : "Y"
                                }
                            }
                        ]
                    },
                    "winningPlan" : {
                        "stage" : "COLLSCAN",
                        "filter" : {
                            "$and" : [ 
                                {
                                    "$or" : [ 
                                        {
                                            "Id" : {
                                                "$eq" : "900004"
                                            }
                                        }, 
                                        {
                                            "relatedID" : {
                                                "$eq" : "900004"
                                            }
                                        }
                                    ]
                                }, 
                                {
                                    "isValid" : {
                                        "$eq" : "Y"
                                    }
                                }
                            ]
                        },
                        "direction" : "forward"
                    },
                    "rejectedPlans" : []
                }
            }
        }
    ],
    "ok" : 1.0
}

How can I get a fast a query for my results?

I am building an application that requires a lot of tables in one database and while the joining and aggregation of data is really nice and seamless, I am starting to wonder if I am creating too many tables in one database rather than organizing better by creating multiple databases. 1. Is this standard practice in enterprise level companies? 2. How do you usually join data from two different databases if its normal to do so? 3. Does this cause latency issues? Any help or guidance would help,

I'm on Windows 10, MySQL Workbench. I'm on MySQL 8.0. Here's the dataset. northwind.sql. https://pastebin.com/bMgjXvfT Objective: **Write a query to get the order ID, customer's name, grand total of each order, and the name of the employee who handled each order. See below the expected first four rows.** Output should look like this. This is the database schema diagram. https://brucebauer.info/assets/ITEC3610/Northwind/Northwind-Sample-Database-Diagram.pdf It's northwind.sql database. This is my query.

SELECT 
    o.orderid,
    c.contactname,
    SUM(od.unitprice * od.quantity),
    CONCAT(e.lastname, ' ', e.firstname) AS emp_name
FROM
    orders o
        INNER JOIN
    customers c ON o.customerid = c.customerid
        INNER JOIN
    orderdetails od ON o.orderid = od.OrderID
        INNER JOIN
    employees e ON o.EmployeeID = e.EmployeeID
GROUP BY emp_name
ORDER BY orderid
LIMIT 4;

But it was producing an error. **Error Code: 1055. Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'northwind.o.OrderID' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by** But if I order by orderid alone, it'll work, Why does it work? Should not it be producing the same error as above?

I have following easy-to-explain task: we have a table, and we should calculate total count of rows grouped by columns. Column number is fixed so we write: SELECT [GroupId] = cr.[GroupId], [OnCreateCount] = SUM(CASE WHEN cr.StatusOfRequest = 1 THEN 1 ELSE 0 END), [NewCount] = SUM(CASE WHEN cr.StatusOfRequest = 2 THEN 1 ELSE 0 END), [CreatedCount] = SUM(CASE WHEN cr.StatusOfRequest = 1 AND THEN 1 ELSE 0 END), [ReviewCount] = SUM(CASE WHEN cr.StatusOfRequest = 3 THEN 1 ELSE 0 END), [RejectedCount] = SUM(CASE WHEN cr.StatusOfRequest = 4 THEN 1 ELSE 0 END), [TotalCount] = COUNT(*) FROM [FK].[CertificateRequest] cr GROUP BY cr.[GroupId] Here OnCreateCount and CreatedCount are the same, but it's wrong. CreatedCount is number of rows with status 1 which are referenced from table [Certificate]. OnCreate is number of rows with status 1 which are **not** referenced from table [Certificate] Here is how I solved it: SELECT [GroupId] = cr.[GroupId], [OnCreateCount] = SUM(CASE WHEN cr.StatusOfRequest = 1 AND c.ID IS NULL THEN 1 ELSE 0 END), [NewCount] = SUM(CASE WHEN cr.StatusOfRequest = 2 THEN 1 ELSE 0 END), [CreatedCount] = SUM(CASE WHEN cr.StatusOfRequest = 1 AND c.ID IS NOT NULL THEN 1 ELSE 0 END), [ReviewCount] = SUM(CASE WHEN cr.StatusOfRequest = 3 THEN 1 ELSE 0 END), [RejectedCount] = SUM(CASE WHEN cr.StatusOfRequest = 4 THEN 1 ELSE 0 END), [TotalCount] = COUNT(*) FROM [FK].[CertificateRequest] cr OUTER APPLY ( SELECT TOP 1 * FROM [FK].[Certificate] c WHERE c.CertificateRequestID = cr.ID ) c GROUP BY cr.[GroupId] But I really don't like OUTER APPLY here which is used just to determine whenever there is any row in referenced table. What is the easiest way to get required result?