<?xml version="1.0" encoding="UTF-8"?>
<!--
  ~ Licensed to the Apache Software Foundation (ASF) under one or more
  ~ contributor license agreements.  See the NOTICE file distributed with
  ~ this work for additional information regarding copyright ownership.
  ~ The ASF licenses this file to You under the Apache License, Version 2.0
  ~ (the "License"); you may not use this file except in compliance with
  ~ the License.  You may obtain a copy of the License at
  ~
  ~     http://www.apache.org/licenses/LICENSE-2.0
  ~
  ~ Unless required by applicable law or agreed to in writing, software
  ~ distributed under the License is distributed on an "AS IS" BASIS,
  ~ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  ~ See the License for the specific language governing permissions and
  ~ limitations under the License.
  -->

<sql-cases>
    <sql-case id="select_group_by_with_sum" value="SELECT SUM(order_id) AS orders_sum, user_id FROM t_order GROUP BY user_id ORDER BY user_id" />
    <sql-case id="select_group_by_with_count" value="SELECT COUNT(order_id) AS orders_count, user_id FROM t_order GROUP BY user_id ORDER BY user_id" />
    <sql-case id="select_group_by_with_max" value="SELECT MAX(order_id) AS max_order_id, user_id FROM t_order GROUP BY user_id ORDER BY user_id" />
    <sql-case id="select_group_by_with_min" value="SELECT MIN(order_id) AS min_order_id, user_id FROM t_order GROUP BY user_id ORDER BY user_id" />
    <sql-case id="select_group_by_with_avg" value="SELECT AVG(order_id) AS orders_avg, user_id FROM t_order GROUP BY user_id ORDER BY user_id" />
    <sql-case id="select_group_by_with_order_by_desc" value="SELECT SUM(order_id) AS orders_sum, user_id FROM t_order GROUP BY user_id ORDER BY orders_sum DESC" />
    <sql-case id="select_group_by_without_grouped_column" value="SELECT count(*) as items_count FROM t_order o JOIN t_order_item i ON o.user_id = i.user_id AND o.order_id = i.order_id WHERE o.user_id IN (?, ?) AND o.order_id BETWEEN ? AND ? GROUP BY o.user_id" />
    <sql-case id="select_group_by_with_limit" value="SELECT user_id FROM t_order GROUP BY user_id ORDER BY user_id LIMIT ?" db-types="H2,MySQL,PostgreSQL" />
    <sql-case id="select_group_by_with_order_by_and_limit" value="SELECT user_id, SUM(order_id) AS orders_sum FROM t_order GROUP BY user_id ORDER BY SUM(order_id) LIMIT ?" db-types="H2,MySQL,PostgreSQL" />
    <sql-case id="select_with_item_alias_match_order_by_and_group_by_items" value="SELECT o.user_id uid FROM t_order o GROUP BY o.user_id ORDER BY o.user_id" db-types="H2,MySQL,SQLServer,PostgreSQL" />
    <sql-case id="select_group_by_with_date_function" value="SELECT date_format(creation_date,  '%%y-%%m-%%d') as creation_date, count(*) as c_number FROM `t_order_item` WHERE order_id in (?, ?) GROUP BY date_format(creation_date, '%%y-%%m-%%d')" db-types="MySQL"/>
    <sql-case id="select_group_by_with_keyword_alias" value="SELECT SUM(order_id) AS orders_sum, user_id as `key` FROM t_order GROUP BY `key`" db-types="MySQL" />
    <sql-case id="select_group_by_with_count_without_column_name" value="SELECT COUNT(order_id) AS orders_count, user_id FROM t_order GROUP BY 2 ORDER BY 2" db-types="MySQL,Oracle,SQLServer,PostgreSQL" />
</sql-cases>
