logo
down
shadow

SQL Server group by?


SQL Server group by?

By : user2173522
Date : October 20 2020, 08:10 PM
help you fix your problem One option is to use the row_number window function. This allows you to establish a row number to the result set. Then you can define the grouping and ordering within the over clause, in this case you want to partition by (group) the name field and order by the id field descending. Finally you filter those results where rn = 1 which returns the max result for each grouping.
code :
select * 
from (
    select *, row_number() over (partition by name order by id desc) rn
    from yourtable
) t
where rn = 1


Share : facebook icon twitter icon
SQL Server : Group By Group Title and Group Sum and Total Sum

SQL Server : Group By Group Title and Group Sum and Total Sum


By : user2298243
Date : March 29 2020, 07:55 AM
I hope this helps you . Create 4 Seperate Sub queries for the different kinds of row
One for the data rows One for the group headers One for the group footers One for the report footer
code :
SELECT 
CASE WHEN d.RowType ='GH' THEN d.Table2Id ELSE NULL END as Table2Id,
CASE WHEN d.RowType ='D' THEN d.Price ELSE NULL END as Price,
CASE WHEN d.RowType ='D' THEN d.Stock ELSE NULL END as Stock,
CASE WHEN d.RowType ='GH' THEN d.Field1 ELSE NULL END as Field1,
CASE WHEN d.RowType ='GH' THEN d.Field2 ELSE NULL END as Field2,
CASE WHEN d.RowType ='GH' THEN d.Field3 ELSE NULL END as Field3,
CASE WHEN d.RowType ='GF' THEN d.Price ELSE NULL END as GroupTotal,
CASE WHEN d.RowType ='RF' THEN d.Price ELSE NULL END as Total
FROM 
(
SELECT 
'D' as RowType,
t1.Table2Id, t1.Price, t1.Stock,t2.Field1,t2.Field2,t2.Field3 FROM Table1 t1 INNER JOIN Table2 t2 ON t1.Table2Id=t2.Id
union all
SELECT 
'GH' as RowType,
 Table2Id,  0 as Price, 0 as Stock,t2.Field1,t2.Field2,t2.Field3 FROM Table1 t1 INNER JOIN Table2 t2 ON t1.Table2Id=t2.Id
GROUP BY Table2Id,t2.Field1,t2.Field2,t2.Field3 
union all
SELECT 
'GF' as RowType,
 Table2Id, SUM(PRice) as Price, 0 as Stock,NULL,NULL,NULL FROM Table1 t1 INNER JOIN Table2 t2 ON t1.Table2Id=t2.Id
GROUP BY Table2Id
union all
SELECT 
'RF' as RowType,
0 as Table2Id, SUM(PRice) as Price, 0 as Stock,null as Field1,null as Field2,null as Field3 FROM Table1 t1 INNER JOIN Table2 t2 ON t1.Table2Id=t2.Id
) d
ORDER BY 
CASE WHEN d.RowType = 'RF' then 1 ELSE 0 END,
 d.Table2Id,
CASE WHEN d.RowType = 'GH' then 0 
WHEN d.RowType = 'D' then 1
WHEN d.RowType = 'GF' then 2
ELSE 4 END
SQL Server How To make A Group Inside A Group - Using GROUP BY

SQL Server How To make A Group Inside A Group - Using GROUP BY


By : bryant
Date : March 29 2020, 07:55 AM
it fixes the issue Imagine what would be possible if the data was made easily available to us, and we clearly understood what the expected final result should look like.
SQL Fiddle
code :
CREATE TABLE Accesslog
    ([EmployeeID] int, [LogDate] datetime, [LogTime] datetime, [Terminal] varchar(11), [InOut] int, [Accepted] int)
;

INSERT INTO Accesslog
    ([EmployeeID], [LogDate], [LogTime], [Terminal], [InOut], [Accepted])
VALUES
    (1009, '2015-10-14 00:00:00', '1900-01-01 06:36:06', 'abcdedghijk', 0, 1),
    (1009, '2015-10-14 00:00:00', '1900-01-01 16:22:41', 'abcdedghijk', 1, 1)
;
select
        EmployeeID
      , LogDate
      , MIN(case when InOut = 0 then cast(LogTime as time) end) as LogTimeMin
      , MAX(case when InOut = 1 then cast(LogTime as time) end) as LogTimeMax
      , COUNT(*) as CountPunches
      , CONVERT(varchar(12),
         DATEADD(minute,
         DATEDIFF(minute,
         MIN(case when InOut = 0 then cast(LogTime as time) end),
         MAX(case when InOut = 1 then cast(LogTime as time) end)), 0), 114) as HoursAttending
from Accesslog
group by
        EmployeeID
      , LogDate
| EmployeeID |          LogDate |  LogTimeMin |  LogTimeMax | CountPunches | HoursAttending |
|------------|------------------|-------------|-------------|--------------|----------------|
|       1009 | October, 14 2015 | 06:36:06.00 | 16:22:41.00 |            2 |   09:46:00:000 |
CREATE TABLE Accesslog
    ([EmployeeID] int, [LogDate] datetime, [LogTime] datetime, [Terminal] varchar(11), [InOut] int, [Accepted] int)
;

INSERT INTO Accesslog
    ([EmployeeID], [LogDate], [LogTime], [Terminal], [InOut], [Accepted])
VALUES
    (1209, '2015-10-14 00:00:00', '1900-01-01 07:00:00', 'abcdedghijk', 0, 1),
    (1209, '2015-10-14 00:00:00', '1900-01-01 12:01:00', 'abcdedghijk', 1, 1),
    (1209, '2015-10-14 00:00:00', '1900-01-01 15:00:00', 'abcdedghijk', 0, 1),
    (1209, '2015-10-14 00:00:00', '1900-01-01 20:02:00', 'abcdedghijk', 1, 1),
    (1009, '2015-10-14 00:00:00', '1900-01-01 08:00:00', 'abcdedghijk', 0, 1),
    (1009, '2015-10-14 00:00:00', '1900-01-01 16:00:00', 'abcdedghijk', 1, 1),
    (1088, '2015-10-15 00:00:00', '1900-01-01 09:00:00', 'aaaa', 0, 1),
    (1088, '2015-10-15 00:00:00', '1900-01-01 15:00:00', 'aaaa', 1, 1)
;
/*
 including (07:00 - 12:00) shift two (15:00 - 20:00) 
 */

WITH CTE as (
        SELECT *, ROW_NUMBER() OVER(PARTITION BY EmployeeID, LogDate, InOut
                                    ORDER BY LogTime ASC) AS shiftno
        FROM Accesslog
             )
SELECT
        ins.EmployeeID
      , ins.LogDate
      , ins.LogTime  as LogTimeIn
      , outs.LogTime as LogTimeOut
      , ins.Accepted + ISNULL(outs.Accepted,0) as CountPunches
      , CONVERT(varchar(12),
         DATEADD(minute,
         DATEDIFF(minute,
         ins.LogTime,
         outs.LogTime), 0), 114) as HoursAttending
FROM CTE  AS ins
INNER JOIN CTE AS outs ON ins.InOut = 0 AND outs.InOut = 1
                      AND ins.EmployeeID = outs.EmployeeID
                      AND ins.LogDate = outs.LogDate
                      AND ins.shiftno = outs.shiftno
| EmployeeID |                   LogDate |                 LogTimeIn |                LogTimeOut | CountPunches | HoursAttending |
|------------|---------------------------|---------------------------|---------------------------|--------------|----------------|
|       1009 | October, 14 2015 00:00:00 | January, 01 1900 08:00:00 | January, 01 1900 16:00:00 |            2 |   08:00:00:000 |
|       1088 | October, 15 2015 00:00:00 | January, 01 1900 09:00:00 | January, 01 1900 15:00:00 |            2 |   06:00:00:000 |
|       1209 | October, 14 2015 00:00:00 | January, 01 1900 07:00:00 | January, 01 1900 12:01:00 |            2 |   05:01:00:000 |
|       1209 | October, 14 2015 00:00:00 | January, 01 1900 15:00:00 | January, 01 1900 20:02:00 |            2 |   05:02:00:000 |
How to group nearby latitude and longitude and assign a Name/Number to that group in SQL Server?

How to group nearby latitude and longitude and assign a Name/Number to that group in SQL Server?


By : choomy
Date : March 29 2020, 07:55 AM
Hope that helps First, let's create a computed geography column that will store the location coordinates. We will use this column to let SQL Server calculate distances for us:
code :
ALTER TABLE #temp
ADD Point_Geolocation AS geography::STPointFromText('POINT(' + CAST(LONGITUDE AS VARCHAR(100))+ ' ' + CAST(LATITUDE AS VARCHAR(100)) +')', 4326) PERSISTED
IF OBJECT_ID('tempdb..#Nearby_Points') IS NOT NULL DROP TABLE #Nearby_Points
CREATE TABLE #Nearby_Points (
        ID_1 VARCHAR(10) NOT NULL,
        ID_2 VARCHAR(10) NOT NULL,
        PRIMARY KEY (ID_1, ID_2)
)

INSERT INTO #Nearby_Points
(
    ID_1,
    ID_2
)
SELECT   t1.ID AS p1_ID
        ,t2.ID AS p2_ID
FROM #temp t1
    INNER JOIN #temp t2
        ON t1.ID < t2.ID
WHERE t1.Point_Geolocation.STDistance(t2.Point_Geolocation) < 40 -- Specify distance criteria here

-- SELECT * FROM #Nearby_Points
IF OBJECT_ID('tempdb..#Clusters') IS NOT NULL DROP TABLE #Clusters
CREATE TABLE #Clusters(
    Cluster_ID INT NOT NULL,
    Point_ID VARCHAR(10) NOT NULL,
    PRIMARY KEY(Cluster_ID, Point_ID)
);

-- This index may improve performance a little
CREATE NONCLUSTERED INDEX IX_Point_ID ON #Clusters(Point_ID);
DECLARE @Rowcount INT

INSERT INTO #Clusters
(
    Cluster_ID,
    Point_ID
)
SELECT   COALESCE((SELECT MAX(Cluster_ID) FROM #Clusters),0) + 1
        ,MIN(np.ID_1)
FROM #Nearby_Points np
WHERE np.ID_1 NOT IN (SELECT Point_ID FROM #Clusters)
HAVING MIN(np.ID_1) IS NOT NULL

SET @Rowcount = @@ROWCOUNT

WHILE @Rowcount > 0
BEGIN

    WHILE @Rowcount > 0
    BEGIN

            INSERT INTO #Clusters
            (
                Cluster_ID,
                Point_ID
            )
            SELECT   Cluster_ID
                    ,Point_ID
            FROM (
                    SELECT   np.ID_2 AS Point_ID
                            ,c.Cluster_ID
                    FROM #Nearby_Points np
                        INNER JOIN #Clusters c
                            ON np.ID_1 = c.Point_ID

                    UNION

                    SELECT   np.ID_1
                            ,c.Cluster_ID
                    FROM #Nearby_Points np
                        INNER JOIN #Clusters c
                            ON np.ID_2 = c.Point_ID
            ) vals
            WHERE NOT EXISTS (
                    SELECT 1
                    FROM #Clusters
                    WHERE Cluster_ID = vals.Cluster_ID
                    AND Point_ID = vals.Point_ID
            )

            SET @Rowcount = @@ROWCOUNT
    END


    INSERT INTO #Clusters
    (
        Cluster_ID,
        Point_ID
    )
    SELECT   COALESCE((SELECT MAX(Cluster_ID) FROM #Clusters),0) + 1
            ,MIN(np.ID_1)
    FROM #Nearby_Points np
    WHERE np.ID_1 NOT IN (SELECT Point_ID FROM #Clusters)
    HAVING MIN(np.ID_1) IS NOT NULL

    SET @Rowcount = @@ROWCOUNT
END
SELECT *
FROM #Clusters c



|Cluster_ID | Point_ID|
|-----------|---------|
|         1 | ID1     |
|         1 | ID2     |
|         1 | ID3     |
|         1 | ID4     |
|         1 | ID5     |
|         1 | ID6     |
|         2 | ID7     |
|         2 | ID8     |
|         2 | ID9     |
Which is better : to group records in a sql server stored procedure or to group in rdlc report?

Which is better : to group records in a sql server stored procedure or to group in rdlc report?


By : NinjaTurtle
Date : March 29 2020, 07:55 AM
help you fix your problem Do it in the database unless you need the details rows available to allow drill down in the report itself.
This means that the resultset passed back to the report will potentially be much smaller and SQL Server will likely have better algorithms and more information about the source data to do the grouping efficiently.
SQL Server : create group of N rows each and give group number for each group

SQL Server : create group of N rows each and give group number for each group


By : user3396534
Date : March 29 2020, 07:55 AM
Does that help Use row_number() to enumerate the rows, arithmetic to assign the group and then take the minimum of the id:
code :
SELECT t.*, MIN(ID) OVER (PARTITION BY grp) as groupnumber       
FROM (SELECT t.*,
            ( (ROW_NUMBER() OVER (ORDER BY ID) - 1) / 3) as grp
      FROM Table
     ) t
ORDER BY ID ASC;
select t.*,
       (case when row_number() over (order by id) % 3 = 0
             then lag(id, 2) over (order by id)
             when row_number() over (order by id) % 3 = 2
             then lag(id, 1) over (order by id)
             else id
        end) as groupnumber
from table t
order by id;
Related Posts Related Posts :
  • SQL Query - Group consecutive items based on condition
  • Users who work in same department
  • Syntax error near column value Vb
  • Oracle Trigger BEFORE INSERT has No data found
  • What kind of join to use on SQL tables
  • Is there a way to add a constant value dynamically to all records returned in Hive?
  • SQL optimization (inner join or selects)
  • EF 6.x, LINQ-to-SQL and raw SQL clauses
  • Simple SQL Variable Assignment Only Returns One Letter: Why?
  • Converting a custom timestamp to date
  • SQL Server : inserting Player vs Player names in to new table from tblEntrants
  • invalid identifier in sql
  • PL/SQL - I keep getting this error when concatenating: PLS-00306: wrong number or types of arguments in call to '||'
  • Count records only from left side of a LEFT JOIN
  • get everything before a string including itself oracle
  • Format Data from Word Doc to SQL using RegEX
  • Conditional formatting on MAX value row
  • MS-Access : selecting data from two tables and only returning you need
  • SQL Server: optimal indexing strategies for many-to-many join
  • DBgrid column very wide
  • PostgreSQL Group values by category, count and calculate percentage
  • MS Access SQL - Most Recent Record for Each Consultant ID
  • Update table: Summary of previous rows without using cursor or while loop
  • PostgreSQL: built-in function to remove substring starting with certain pattern
  • ORA-00909: invalid number of arguments
  • How to summarize all possible combinations of variables?
  • Select Column within a Column SQL
  • PostgreSQL Inserting 2 relationships at once
  • T sql - How to store results from a dynamic query using EXEC or EXECUTE sp_executesql
  • How do I parse my json into CSV using regex?
  • Reverse foreign key cascading (or how to collect database garbage)
  • SQL Pivot Questions
  • Insert records into a table with a condition in SQL Server 2016
  • display null value using rank functions in oracle sql
  • SQL - Get count of group by column but also select top item of group
  • How to add an array of datarows into an exisitng table inside my database
  • There is no unique constraint matching given keys for referenced table "employee" 1
  • SQL: Unable to SELECT joined column
  • How to find out how much space a SQL Server table uses?
  • Window function to remove specific records from SQL Server dataset
  • How to add a column for each day in sql?
  • Create group column based on the specific rows
  • Not sure if this consistitues a transitive dependency
  • How to compare the values in a column to a long list in SQL Server
  • Preserving data format Decimal(6,5) from vba to sql
  • Oracle Query to rollup QTY by Year- only last 3 years
  • SQL - Calculate 2 columns and view result to another to column
  • Divide or Multiply according to a condition (Improving query)
  • PostgreSQL unnest() with consecutive integers grouped by number
  • SQL to limit output to certain months and years
  • VARCHAR TIME TO GET THE DIFFERENCE
  • SQL conditional constraint on multiple columns being unique
  • Optimize a SQL select query in a loop
  • BTEQ Teradata Import Multiple files into one table
  • Update SQL datetime column with oldest values of another table column?
  • Is INSERT ... SELECT an atomic transaction?
  • SQL query completed successfully but not results
  • SQL sub select returning multiple values
  • Verify condition on two columns
  • SQL conditional field, first match JOIN
  • shadow
    Privacy Policy - Terms - Contact Us © voile276.org