querybuilder/QueryBuilder.Tests/AggregateTests.cs at 68a11be1d94fc454c7ecc2eb5e8932ba0bec64a8 · sqlkata/querybuilder · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
using SqlKata.Compilers;
using SqlKata.Tests.Infrastructure;
using System.Collections.Generic;
using Xunit;

namespace SqlKata.Tests
{
    public class AggregateTests : TestSupport
    {
        [Fact]
        public void Count()
        {
            var query = new Query("A").AsCount();

            var c = Compile(query);

            Assert.Equal("SELECT COUNT(*) AS [count] FROM [A]", c[EngineCodes.SqlServer]);
            Assert.Equal("SELECT COUNT(*) AS `count` FROM `A`", c[EngineCodes.MySql]);
            Assert.Equal("SELECT COUNT(*) AS \"count\" FROM \"A\"", c[EngineCodes.PostgreSql]);
            Assert.Equal("SELECT COUNT(*) AS \"COUNT\" FROM \"A\"", c[EngineCodes.Firebird]);
        }

        [Fact]
        public void CountMultipleColumns()
        {
            var query = new Query("A").AsCount(new[] { "ColumnA", "ColumnB" });

            var c = Compile(query);

            Assert.Equal("SELECT COUNT(*) AS [count] FROM (SELECT 1 FROM [A] WHERE [ColumnA] IS NOT NULL AND [ColumnB] IS NOT NULL) AS [countQuery]", c[EngineCodes.SqlServer]);
        }

        [Fact]
        public void DistinctCount()
        {
            var query = new Query("A").Distinct().AsCount();

            var c = Compile(query);

            Assert.Equal("SELECT COUNT(*) AS [count] FROM (SELECT DISTINCT * FROM [A]) AS [countQuery]", c[EngineCodes.SqlServer]);
        }

        [Fact]
        public void DistinctCountMultipleColumns()
        {
            var query = new Query("A").Distinct().AsCount(new[] { "ColumnA", "ColumnB" });

            var c = Compile(query);

            Assert.Equal("SELECT COUNT(*) AS [count] FROM (SELECT DISTINCT [ColumnA], [ColumnB] FROM [A]) AS [countQuery]", c[EngineCodes.SqlServer]);
        }

        [Fact]
        public void Average()
        {
            var query = new Query("A").AsAverage("TTL");

            var c = Compile(query);

            Assert.Equal("SELECT AVG([TTL]) AS [avg] FROM [A]", c[EngineCodes.SqlServer]);
        }

        [Fact]
        public void Sum()
        {
            var query = new Query("A").AsSum("PacketsDropped");

            var c = Compile(query);

            Assert.Equal("SELECT SUM([PacketsDropped]) AS [sum] FROM [A]", c[EngineCodes.SqlServer]);
        }

        [Fact]
        public void Max()
        {
            var query = new Query("A").AsMax("LatencyMs");

            var c = Compile(query);

            Assert.Equal("SELECT MAX([LatencyMs]) AS [max] FROM [A]", c[EngineCodes.SqlServer]);
        }

        [Fact]
        public void Min()
        {
            var query = new Query("A").AsMin("LatencyMs");

            var c = Compile(query);

            Assert.Equal("SELECT MIN([LatencyMs]) AS [min] FROM [A]", c[EngineCodes.SqlServer]);
        }

        [Fact]
        public void HavingAggregate()
        {
            var query = new Query().From("TABLENAME").GroupBy("Title").HavingSum("Title", ">", 21).OrHavingAvg("Title", ">", 21).HavingCount("Title", ">", 21).Select("Title");

            var compiler = Compile(query);

            Assert.Equal("SELECT [Title] FROM [TABLENAME] GROUP BY [Title] HAVING SUM([Title]) > 21 OR AVG([Title]) > 21 AND COUNT([Title]) > 21", compiler[EngineCodes.SqlServer]);

            Assert.Equal("SELECT \"Title\" FROM \"TABLENAME\" GROUP BY \"Title\" HAVING SUM(\"Title\") > 21 OR AVG(\"Title\") > 21 AND COUNT(\"Title\") > 21", compiler[EngineCodes.PostgreSql]);

            Assert.Equal("SELECT \"Title\" FROM \"TABLENAME\" GROUP BY \"Title\" HAVING SUM(\"Title\") > 21 OR AVG(\"Title\") > 21 AND COUNT(\"Title\") > 21", compiler[EngineCodes.Oracle]);

            Assert.Equal("SELECT `Title` FROM `TABLENAME` GROUP BY `Title` HAVING SUM(`Title`) > 21 OR AVG(`Title`) > 21 AND COUNT(`Title`) > 21", compiler[EngineCodes.MySql]);

            Assert.Equal("SELECT \"Title\" FROM \"TABLENAME\" GROUP BY \"Title\" HAVING SUM(\"Title\") > 21 OR AVG(\"Title\") > 21 AND COUNT(\"Title\") > 21", compiler[EngineCodes.Sqlite]);
        }

        [Fact]
        public void HavingAggregateWithKeyword()
        {
            var query = new Query().From("TABLENAME").GroupBy("Title").HavingDistinctSum("Title", ">", 21).OrHavingAllCount("Title", ">", 21).Select("Title");

            var compiler = Compile(query);

            Assert.Equal("SELECT [Title] FROM [TABLENAME] GROUP BY [Title] HAVING SUM(DISTINCT [Title]) > 21 OR COUNT(ALL [Title]) > 21", compiler[EngineCodes.SqlServer]);

            Assert.Equal("SELECT \"Title\" FROM \"TABLENAME\" GROUP BY \"Title\" HAVING SUM(DISTINCT \"Title\") > 21 OR COUNT(ALL \"Title\") > 21", compiler[EngineCodes.PostgreSql]);

            Assert.Equal("SELECT \"Title\" FROM \"TABLENAME\" GROUP BY \"Title\" HAVING SUM(DISTINCT \"Title\") > 21 OR COUNT(ALL \"Title\") > 21", compiler[EngineCodes.Oracle]);

            Assert.Equal("SELECT `Title` FROM `TABLENAME` GROUP BY `Title` HAVING SUM(DISTINCT `Title`) > 21 OR COUNT(ALL `Title`) > 21", compiler[EngineCodes.MySql]);

            Assert.Equal("SELECT \"Title\" FROM \"TABLENAME\" GROUP BY \"Title\" HAVING SUM(DISTINCT \"Title\") > 21 OR COUNT(ALL \"Title\") > 21", compiler[EngineCodes.Sqlite]);

        }

        [Fact]
        public void HavingAggregateWithFilter()
        {
            var query = new Query().From("TABLENAME").GroupBy("Title").HavingSum(d => d.WhereIn("Title", new List<int>() { 11, 12, 13 })).OrHavingAvg(d => d.WhereBetween("Title", 11, 12)).HavingCount(d => d.WhereLike("Title", "having")).HavingCount(d => d).Select("Title");

            var compiler = Compile(query);

            Assert.Equal("SELECT [Title] FROM [TABLENAME] GROUP BY [Title] HAVING SUM([Title]) IN (11, 12, 13) OR AVG([Title]) BETWEEN 11 AND 12 AND COUNT(LOWER([Title])) like 'having'", compiler[EngineCodes.SqlServer]);

            Assert.Equal("SELECT \"Title\" FROM \"TABLENAME\" GROUP BY \"Title\" HAVING SUM(\"Title\") IN (11, 12, 13) OR AVG(\"Title\") BETWEEN 11 AND 12 AND COUNT(\"Title\") ilike 'having'", compiler[EngineCodes.PostgreSql]);

            Assert.Equal("SELECT \"Title\" FROM \"TABLENAME\" GROUP BY \"Title\" HAVING SUM(\"Title\") IN (11, 12, 13) OR AVG(\"Title\") BETWEEN 11 AND 12 AND COUNT(LOWER(\"Title\")) like 'having'", compiler[EngineCodes.Oracle]);

            Assert.Equal("SELECT `Title` FROM `TABLENAME` GROUP BY `Title` HAVING SUM(`Title`) IN (11, 12, 13) OR AVG(`Title`) BETWEEN 11 AND 12 AND COUNT(LOWER(`Title`)) like 'having'", compiler[EngineCodes.MySql]);

            Assert.Equal("SELECT \"Title\" FROM \"TABLENAME\" GROUP BY \"Title\" HAVING SUM(\"Title\") IN (11, 12, 13) OR AVG(\"Title\") BETWEEN 11 AND 12 AND COUNT(LOWER(\"Title\")) like 'having'", compiler[EngineCodes.Sqlite]);

        }
    }
}