CODEWITHSUNDEEP
sqlsql-server
user3392650
user3392650

Reputation: 3

How to get Count of Records in sql

My sql table structure is : 

Order No.   |  Carton No  | ...  .& so on
D1               1
D1               2
D1               3
D1               4
D2               5
D2               6

I want to get the count of Carton No group By Order No . and the records included in the count. like this -

OrderNo  | Count | Carton No
D1          4        1,2,3,4
D2          2         5,6

Is it possible to get the desired result using sql query.

Upvotes: 0

Views: 1432

Answers (3)

Simon
Simon

Reputation: 43

DECLARE @MYTABLE TABLE(ID NVARCHAR(5),zone CHAR(1))
INSERT INTO @MYTABLE VALUES
('D1',1),('D1',2),('D1',3),
('D1',4),('D2',5),('D2',6)
SELECT  t.ID,COUNT(ID) AS [Count]
       ,STUFF((SELECT '/'+ zone
               FROM @MYTABLE
               WHERE ID = t.ID
               FOR XML PATH(''),TYPE).
               value('.','NVARCHAR(MAX)'),1,1,'') AS Zones
FROM @MYTABLE t
GROUP BY t.ID

Upvotes: 0

Bacon Bits
Bacon Bits

Reputation: 32145

The COUNT() function gets you the count, but the comma-delimited list is much more difficult. As far as ANSI standard SQL, no. What you're asking denormalizes the query results, therefore the standard SQL response is to do this in the application, not in SQL. It's a display issue.

Many RDBMSs do have vendor-specific aggregate functions that do this, however:

Oracle: wm_concat() and LISTAGG(). wm_concat() is older, but it was never considered a documented function.

MySQL: GROUP_CONCAT().

PostgreSQL: array_agg() and string_agg(). Older versions may not have both or either function.

MS SQL: This one is extremely difficult. You generally have to use STUFF() in combination with FOR XML PATH(''). However, this behavior is not a documented behavior, so should be considered deprecated. Prior to SQL Server 2005's introduction of the FOR XML PATH('') statement, I don't believe there was any good way to do this.

SQLite: GROUP_CONCAT().

Upvotes: 0

Mihai
Mihai

Reputation: 26784

SELECT "Order No.",COUNT("Order No.")as Count ,
listagg("Carton No" , ',') within group (order by "Carton No") "Carton No"
FROM tableName
GROUP BY "Order No."

SQL Server

SELECT [Order No.],COUNT([Order No.])as Count ,
[Carton No]=STUFF((SELECT ','+[Carton No] FROM tableName WHERE [Order No.]=A.[Order No.]
FOR XML PATH('')) , 1 , 1 , '' )
FROM 
tableName A
GROUP BY [Order No.]

Upvotes: 2

Related Questions