T-SQL - Group by with Like - Is This Possible

T-SQL - GROUP BY with LIKE - is this possible?

You need an expression that returns "Fall_2009" or "Spring_2009", and then group on that expression. eg:

-- identify each pattern individually w/ a case statement
SELECT
  CASE
    WHEN column_x LIKE '%Fall[_]2009'   THEN 'Fall 2009'
    WHEN column_x LIKE '%Spring[_]2009' THEN 'Spring 2009'
  END AS group_by_value
, COUNT(*) AS group_by_count
FROM Table1 a
GROUP BY 
  CASE
    WHEN column_x LIKE '%Fall[_]2009'   THEN 'Fall 2009'
    WHEN column_x LIKE '%Spring[_]2009' THEN 'Spring 2009'
  END

or

-- strip all characters up to the first space or dash
SELECT 
  STUFF(column_x,1,PATINDEX('%[- ]%',column_x),'') AS group_by_value
, COUNT(*) as group_by_count
FROM Table1 a
GROUP BY 
  STUFF(column_x,1,PATINDEX('%[- ]%',column_x),'')

or

-- join to a (pseudo) table of pattern masks
SELECT b.Label, COUNT(*)
FROM Table1 a
JOIN (
  SELECT '%Fall[_]2009'  , 'Fall, 2009' UNION ALL
  SELECT '%Spring[_]2009', 'Spring, 2009'
  ) b (Mask, Label) ON a.column_x LIKE b.Mask
GROUP BY b.Label

SQL: Is it possible to 'group by' according to 'like' function's results?

Sure:

WITH Fruits AS (
    SELECT 
        CASE 
           WHEN m.str LIKE '%APPLE%' THEN 'Apple'
           WHEN m.str LIKE '%ORANGE%' THEN 'Orange' 
        END AS FruitType           
    FROM MESA m
    WHERE m.str LIKE '%FRUIT%')
SELECT FruitType, COUNT(*) 
FROM Fruits
WHERE FruitType IN ('Apple', 'Orange')
GROUP BY FruitType;

Is it possible to group rows by difference to each other in T-SQL

In SQL Server 2012+:

Using the window function lag() in a common table expression to get the datediff() of the current row starttime compared to the previous row value for endtime, and then sum() over() with conditional aggregation (comparision to the hardcoded value) to generate the batch:

;with cte as (
select *
  , datediff(second,lag(endtime) over (order by starttime),starttime) as prev_dat
from timings
)
select id, starttime, endtime, duration
  , sum(case when coalesce(prev_dat,31)>30 then 1 else 0 end) over (
    order by starttime
    ) as batch
from cte

rextester demo: http://rextester.com/OVNF90739

returns:

+----+---------------------+---------------------+----------+-------+
| id |      starttime      |       endtime       | duration | batch |
+----+---------------------+---------------------+----------+-------+
|  1 | 2017-10-06 10:59:48 | 2017-10-06 10:59:58 |       10 |     1 |
|  2 | 2017-10-06 11:00:02 | 2017-10-06 11:00:06 |        4 |     1 |
|  3 | 2017-10-06 11:00:15 | 2017-10-06 11:00:22 |        7 |     1 |
|  4 | 2017-10-06 11:00:30 | 2017-10-06 11:00:39 |        9 |     1 |
|  5 | 2017-10-06 15:34:31 | 2017-10-06 15:34:45 |       14 |     2 |
|  6 | 2017-10-06 15:34:48 | 2017-10-06 15:34:56 |        8 |     2 |
|  7 | 2017-10-06 15:34:52 | 2017-10-06 15:34:59 |        7 |     2 |
+----+---------------------+---------------------+----------+-------+

How do I perform a GROUP BY on an aliased column in SQL Server?

You pass the expression you want to group by rather than the alias

SELECT       LastName + ', ' + FirstName AS 'FullName'
FROM         customers
GROUP BY      LastName + ', ' + FirstName

SQL Query with optional aggregate and group possible?

I think you can do the following:

declare @sumIt bit
set @sumIt = 1

select ID, Name,
       (CASE WHEN @sumIt=1 THEN sum(Time) ELSE min(Time) END) [timeCol]
from Visits
where ID = 123
Group by ID, Name, (CASE WHEN @sumIt=1 THEN '' ELSE Time END) 

You can apply an aggregation function to the group by variables. This isn't commonly done, but it solves your problem.

SQL Group By with an Order By

In all versions of MySQL, simply alias the aggregate in the SELECT list, and order by the alias:

SELECT COUNT(id) AS theCount, `Tag` from `images-tags`
GROUP BY `Tag`
ORDER BY theCount DESC
LIMIT 20

SQL group by selecting top rows with possible nulls

You can combine two queries with UNION ALL. E.g.:

select id, name, create_time, group_id
from mytable
where group_id is not null
and not exists
(
  select null
  from mytable older
  where older.group_id = mytable.group_id
  and older.create_time < mytable.create_time  
)
union all
select id, name, create_time, group_id
from mytable
where group_id is null
order by id;

This is standard SQL and very basic at that. It should work in about every RDBMS.

As to pagination: This is usually costly, as you run the same query again and again in order to always pick the "next" part of the result, instead of running the query only once. The best approach is usually to use the primary key to get to the next part so an index on the key can be used. In above query we'd ideally add where id > :last_biggest_id to the queries and limit the result, which would be fetch next <n> rows only in standard SQL. Everytime we run the query, we use the last read ID as :last_biggest_id, so we read on from there.

Variables, however, are dealt with differently in the various DBMS; most commonly they are preceded by either a colon, a dollar sign or an at sign. And the standard fetch clause, too, is supported by only some DBMS, while others have a LIMIT or TOP clause instead.

If these little differences make it impossible to apply them, then you must find a workaround. For the variable this can be a one-row-table holding the last read maximum ID. For the fetch clause this can mean you simply fetch as many rows as you need and stop there. Of course this isn't ideal, as the DBMS doesn't know then that you only need the next n rows and cannot optimize the execution plan accordingly.

And then there is the option not to do the pagination in the DBMS, but read the complete result into your app and handle pagination there (which then becomes a mere display thing and allocates a lot of memory of course).

SQL - using alias in Group By

SQL is implemented as if a query was executed in the following order:

1. FROM clause
2. WHERE clause
3. GROUP BY clause
4. HAVING clause
5. SELECT clause
6. ORDER BY clause

For most relational database systems, this order explains which names (columns or aliases) are valid because they must have been introduced in a previous step.

So in Oracle and SQL Server, you cannot use a term in the GROUP BY clause that you define in the SELECT clause because the GROUP BY is executed before the SELECT clause.

There are exceptions though: MySQL and Postgres seem to have additional smartness that allows it.

---

Related Topics