Creating User defined aggregation for SQL

Question

Here’s a sample of what I have in mind. When you query tbl with the aggregation function you should be this Result

Tally Agregation function

Table: tbl  

Tag length              
abc 8               
cde 8               
fgh 10

SQL:

SELECT aggTally(Tag, Length) FROM tbl

Result:

2/8   
1/10

I am very new to C#, so How do I create this, and have the dll for use?

Why do you pass the Tag to the aggregate function? It doesn't appear to affect the result. And why not just something like `select Length, count() freq from tbl group by Length`? — David Browne - Microsoft, Jul 12 '19 at 00:28

kcooper · Answer 1 · 2019-07-12T21:16:58.043

I don't see how Tag is used as mentioned above but the below code returns

2/8 1/10

->

    private static void TotalsByLength()
    {
        List<Tuple<string, int>> tagdata = new List<Tuple<string, int>>
        {
            new Tuple<string, int>("abs", 8),
            new Tuple<string, int>("cde", 8),
            new Tuple<string, int>("fgh", 10)
        };

        var tagcounts = from p in tagdata
            group p.Item2 by p.Item2 into g
            orderby g.Count() descending
            select new { g.Key, TotalOccurrence = g.Count() };

        foreach (var s in tagcounts)
        {
            Console.WriteLine("{0}/{1}", s.TotalOccurrence, s.Key );
        }
    }

score 0 · Answer 2 · edited Jul 12 '19 at 21:42

0

T-SQL approach:

CREATE TABLE dbo.TestTable (tag CHAR(3), taglen INT)
GO

INSERT INTO dbo.TestTable VALUES ('abs',8), ('cde', 8), ('fgh',10)
GO

;WITH TagTotal AS (SELECT taglen, COUNT(*) AS totallengthbytag
FROM dbo.TestTable
GROUP BY taglen)
SELECT a.totallengthbytag, a.taglen
FROM TagTotal a

edited Jul 12 '19 at 21:42

Matthew Whited

22,160
4
52
69

answered Jul 12 '19 at 21:39

kcooper

1
3

Creating User defined aggregation for SQL

2 Answers2