How to Use T-SQL Group By

How do I use T-SQL Group By

To retrieve the number of widgets from each widget category that has more than 5 widgets, you could do this:

SELECT WidgetCategory, count(*)
FROM Widgets
GROUP BY WidgetCategory
HAVING count(*) > 5

The "having" clause is something people often forget about, instead opting to retrieve all their data to the client and iterating through it there.

How to use group by with union in T-SQL

GROUP BY 1

I've never known GROUP BY to support using ordinals, only ORDER BY. Either way, only MySQL supports GROUP BY's not including all columns without aggregate functions performed on them. Ordinals aren't recommended practice either because if they're based on the order of the SELECT - if that changes, so does your ORDER BY (or GROUP BY if supported).

There's no need to run GROUP BY on the contents when you're using UNION - UNION ensures that duplicates are removed; UNION ALL is faster because it doesn't - and in that case you would need the GROUP BY...

Your query only needs to be:

SELECT a.id,
       a.time
  FROM dbo.TABLE_A a
UNION
SELECT b.id,
       b.time
  FROM dbo.TABLE_B b

GROUP BY with CASE - tsql

I believe a CTE would simplfy what you're trying to do:

;WITH WT AS (
    SELECT 
        ReportingDate
    ,   PortfolioID
    ,   PortfolioNme

    ,   CASE
            WHEN @ReportType = 'GeoCountry' 
              THEN Infoportal.dbo.fn_Generic_ProperCase(Country)
            WHEN @ReportType = 'GeoEquity'  
              THEN Region
        END AS Country
    ,   Percentage
FROM @Worktable
WHERE IssueType1 <> '010' AND IssueType2 <> '055'
)
SELECT 
        ReportingDate
    ,   PortfolioID
    ,   PortfolioNme
    ,   Country
    ,   RANK() OVER (
          PARTITION BY PortfolioID 
          ORDER BY SUM(Percentage) DESC, Country) AS [Rank]
    ,   SUM(Percentage) AS [Weight]
FROM WT
GROUP BY
        ReportingDate
    ,   PortfolioID
    ,   PortfolioNme
    ,   Country

T-SQL Group by with a where clause

You can do this by adding a WHERE clause which will return rows with either US or CN:

select distinct Masterid
from yourtable
where cc in ('US', 'CN')
  and NLCLA = 1
  AND NLDES = 1

See SQL Fiddle with Demo

If you want the result to include both the CN and US, then you can use:

select Masterid
from yourtable
where cc in ('US', 'CN')
  and NLCLA = 1
  AND NLDES = 1
group by masterid
having count(distinct cc) = 2

See SQL Fiddle with Demo.

Another way that this could be done is using an EXISTS to get the list of MasterIds with both the US and CN. You then place the other filters in the WHERE clause and not in the subquery.

select distinct masterid
from yourtable t1
where exists (select Masterid
              from yourtable t2
              where cc in ('US', 'CN')
                and t1.masterid = t2.masterid
              group by masterid
              having count(distinct cc) = 2)
  and NLCLA = 1
  and NLDES = 1;

See SQL Fiddle with Demo

T-SQL SELECT with GROUP BY id

Since you want to get all records from table People, you need to join it with Notes by using LEFT JOIN so any user without any record on Notes will be included in the list with thev value of totalCount with zero.

SELECT  a.ID, a.Name, a.Age,
        COUNT(b.FK_Author) totalCount
FROM    People a
        LEFT JOIN Notes b
            ON a.ID = b.FK_Author
GROUP   BY a.ID, a.Name, a.Age

• SQLFiddle Demo

To further gain more knowledge about joins, kindly visit the link below:

• Visual Representation of SQL Joins

OUTPUT

╔════╦═══════╦═════╦════════════╗
║ ID ║ NAME  ║ AGE ║ TOTALCOUNT ║
╠════╬═══════╬═════╬════════════╣
║  1 ║ John  ║  12 ║          2 ║
║  2 ║ Annie ║  29 ║          2 ║
║  3 ║ John  ║  44 ║          1 ║
╚════╩═══════╩═════╩════════════╝

TSQL Group by Column with Multiple Values

It can be done easily using recursive CTE:

WITH DataSource AS
(
    SELECT DS1.*
    FROM @Data DS1
    INNER JOIN @Data DS2
        ON DS1.[T_Id] = DS2.[T_Id]
        AND DS1.[N_Id] = DS2.[N_Id]
        AND DS1.[Id] = DS2.[Id] + 1
        AND DS1.[Fg] = 2
        AND DS2.[Fg] = 1
    UNION ALL
    SELECT DS1.*
    FROM @Data DS1
    INNER JOIN DataSource DS2
        ON DS1.[T_Id] = DS2.[T_Id]
        AND DS1.[N_Id] = DS2.[N_Id]
        AND DS1.[Id] = DS2.[Id] - 1
        AND DS1.[Fg] = 1
)
SELECT *
FROM DataSource
ORDER BY Id

The idea is simple. The first part of the query gets all valid records with fg = 2 - valid means there is record before this one with fg = 1 from the same group.

Then in the recursive part we are getting all records smaller then initial ones, that has fg = 1.

TSQL with Group By Count and Rank

If you want a rank column, you can use it with group by:

SELECT n.Name, COUNT(*) as TOTALS,
       RANK() OVER (ORDER BY COUNT(*) DESC) as Total_Rank
FROM @Name n
GROUP BY n.Name
ORDER BY TOTALS DESC;

How do I create table in SQL Server using group by query

Try this:

select id,sum(marks) as marks 
INTO TEST
from student
group by id

Using INTO clause you are allowed to materialized the result in a table (it can be temporary table as well). There are few limitations - columns must have name (alias) and duplicate names are not allowed.

---

Related Topics