SQL group by sample query

Daniel Liu Daniel Liu

Daniel Liu

Published Jul 7, 2022
+ Follow

query 1: --use group by rollup()

select isNull(CONVERT(VARCHAR(4), FILED_RECEIVED_DATE, 126),

case when grouping(CONVERT(VARCHAR(4), FILED_RECEIVED_DATE, 126))=0 then 'unknown' else 'AllYear' end) as [year]

,isNull(CONVERT(VARCHAR(7), FILED_RECEIVED_DATE, 126),

case when grouping(CONVERT(VARCHAR(7), FILED_RECEIVED_DATE, 126))=0 then 'unknown' else 'AllMonth' end )AS [Year-Month]

,isNull([CRTT_DESCRIPTION_X],

case when grouping([CRTT_DESCRIPTION_X])=0 then 'unknown' else 'AllDistrict' end) as District

,count(FK_CASE_NO_Q) as [Count]

from [dbo].[table1]

where FK_CASE_NO_Q is not null

group by rollup(CONVERT(VARCHAR(4), FILED_RECEIVED_DATE, 126), CONVERT(VARCHAR(7), FILED_RECEIVED_DATE, 126),[CRTT_DESCRIPTION_X])

order by CONVERT(VARCHAR(4), FILED_RECEIVED_DATE, 126), CONVERT(VARCHAR(7), FILED_RECEIVED_DATE, 126),[CRTT_DESCRIPTION_X]


query 2: -- use concatenated rollup, rollup order change dataset displaying order

select isNull(CONVERT(VARCHAR(4), FILED_RECEIVED_DATE, 126),

case when grouping(CONVERT(VARCHAR(4), FILED_RECEIVED_DATE, 126))=0 then 'unknown' else 'AllYear' end) as [year]

,isNull(CONVERT(VARCHAR(7), FILED_RECEIVED_DATE, 126),

case when grouping(CONVERT(VARCHAR(7), FILED_RECEIVED_DATE, 126))=0 then 'unknown' else 'AllMonth' end )AS [Year-Month]

,isNull([CRTT_DESCRIPTION_X],

case when grouping([CRTT_DESCRIPTION_X])=0 then 'unknown' else 'AllDistrict' end) as District

,count(FK_CASE_NO_Q) as [Count]

from [dbo].[table1]

where FK_CASE_NO_Q is not null

group by rollup([CRTT_DESCRIPTION_X])

,rollup(CONVERT(VARCHAR(4), FILED_RECEIVED_DATE, 126), CONVERT(VARCHAR(7), FILED_RECEIVED_DATE, 126))

order by CONVERT(VARCHAR(4), FILED_RECEIVED_DATE, 126), CONVERT(VARCHAR(7), FILED_RECEIVED_DATE, 126),[CRTT_DESCRIPTION_X]



query 3: --group by GROUPING SETS(), equivalent of Union ALL of specified groups.

select distinct CONVERT(VARCHAR(4), FILED_RECEIVED_DATE, 23) as [year]

,CONVERT(VARCHAR(7), FILED_RECEIVED_DATE, 126) AS [Year-Month]

,isNull([CRTT_DESCRIPTION_X],'unknown') as district

,count(*) as [Count]

,sum(count(*)) over (partition by CONVERT(VARCHAR(7), FILED_RECEIVED_DATE, 126)) as [CountByMonth]

,sum(count(*)) over (partition by CONVERT(VARCHAR(4), FILED_RECEIVED_DATE, 23)) as [CountByYear]

,sum(count(*)) over (partition by isNull([CRTT_DESCRIPTION_X],'unknown')) as [CountByDistrict]

from [dbo].[table1]

group by GROUPING SETS ((CONVERT(VARCHAR(4), FILED_RECEIVED_DATE, 23),CONVERT(VARCHAR(7), FILED_RECEIVED_DATE, 126),isNull([CRTT_DESCRIPTION_X],'unknown')),())

order by CONVERT(VARCHAR(4), FILED_RECEIVED_DATE, 23), CONVERT(VARCHAR(7), FILED_RECEIVED_DATE, 126)


query 4: ----rollup cube --list all possible combination of columns in cube list and grand total

select isNull(CONVERT(VARCHAR(4), FILED_RECEIVED_DATE, 126),

case when grouping(CONVERT(VARCHAR(4), FILED_RECEIVED_DATE, 126))=0 then 'unknown' else 'AllYear' end) as [year]

,isNull(CONVERT(VARCHAR(7), FILED_RECEIVED_DATE, 126),

case when grouping(CONVERT(VARCHAR(7), FILED_RECEIVED_DATE, 126))=0 then 'unknown' else 'AllMonth' end )AS [Year-Month]

,isNull([CRTT_DESCRIPTION_X],

case when grouping([CRTT_DESCRIPTION_X])=0 then 'unknown' else 'AllDistrict' end) as District

,count(FK_CASE_NO_Q) as [Count]

from [dbo].[table1]

where FK_CASE_NO_Q is not null

group by cube(CONVERT(VARCHAR(4), FILED_RECEIVED_DATE, 126), CONVERT(VARCHAR(7), FILED_RECEIVED_DATE, 126),[CRTT_DESCRIPTION_X])

order by CONVERT(VARCHAR(4), FILED_RECEIVED_DATE, 126), CONVERT(VARCHAR(7), FILED_RECEIVED_DATE, 126),[CRTT_DESCRIPTION_X]


query 5: ---- use separate rollup, rollup order change dataset displaying order

select isNull(CONVERT(VARCHAR(4), FILED_RECEIVED_DATE, 126),

case when grouping(CONVERT(VARCHAR(4), FILED_RECEIVED_DATE, 126))=0 then 'unknown' else 'AllYear' end) as [year]

,isNull(CONVERT(VARCHAR(7), FILED_RECEIVED_DATE, 126),

case when grouping(CONVERT(VARCHAR(7), FILED_RECEIVED_DATE, 126))=0 then 'unknown' else 'AllMonth' end )AS [Year-Month]

,isNull([CRTT_DESCRIPTION_X],

case when grouping([CRTT_DESCRIPTION_X])=0 then 'unknown' else 'AllDistrict' end) as District

,count(FK_CASE_NO_Q) as [Count]

from [dbo].[tst_STAT_Date_Error_Adult]

where FK_CASE_NO_Q is not null

group by rollup([CRTT_DESCRIPTION_X])

,rollup(CONVERT(VARCHAR(4), FILED_RECEIVED_DATE, 126))

,rollup(CONVERT(VARCHAR(7), FILED_RECEIVED_DATE, 126))

order by CONVERT(VARCHAR(4), FILED_RECEIVED_DATE, 126), CONVERT(VARCHAR(7), FILED_RECEIVED_DATE, 126),[CRTT_DESCRIPTION_X]


query 6: --over() is more efficeint than subuery, return less rows ,()) =grad total

select distinct CONVERT(VARCHAR(4), FILED_RECEIVED_DATE, 23) as [year]

,CONVERT(VARCHAR(7), FILED_RECEIVED_DATE, 126) AS [Year-Month]

,isNull([CRTT_DESCRIPTION_X],'unknown') as district

,count(*) as [Count]

,sum(count(*)) over (partition by CONVERT(VARCHAR(7), FILED_RECEIVED_DATE, 126)) as [CountByMonth]

,sum(count(*)) over (partition by CONVERT(VARCHAR(4), FILED_RECEIVED_DATE, 23)) as [CountByYear]

,sum(count(*)) over (partition by isNull([CRTT_DESCRIPTION_X],'unknown')) as [CountByDistrict]

from [dbo].[tst_STAT_Date_Error_Adult]

group by GROUPING SETS (

(CONVERT(VARCHAR(4), FILED_RECEIVED_DATE, 23),CONVERT(VARCHAR(7), FILED_RECEIVED_DATE, 126))

,isNull([CRTT_DESCRIPTION_X],'unknown'),())

order by CONVERT(VARCHAR(4), FILED_RECEIVED_DATE, 23), CONVERT(VARCHAR(7), FILED_RECEIVED_DATE, 126)

To view or add a comment, sign in

More articles by Daniel Liu

  • database table partition
    Oct 22, 2024

    database table partition

    Some database tables grow and become a super large tables. Usually we either archive or partition the large tables.

  • unable to access to SQL server database diagram
    Jan 26, 2023

    unable to access to SQL server database diagram

    I login as sa to a SQL server, trying to create a new database diagram, but unable to access it. Got following error…

  • Validate XML against a schema
    Dec 22, 2020

    Validate XML against a schema

    private static string ValidateXML(string xsd, string strXml,string emailfrom,string emailto) { string ret = ""; string…

  • C# get first friday in any month, any year
    Dec 22, 2020

    C# get first friday in any month, any year

    public string GetFirstFriday(int year,int month) { string friday=null; for (int i = 1; i < 8; i++) { var dtmDate = new…

  • Blob data and PDF conversion in C#
    Feb 20, 2020

    Blob data and PDF conversion in C#

    save Blob data from oracle to PDF file: private static void SaveBlobToPDF(string constr, string filing_id) { using…

  • Replace Apostrophe in SQL linked server query
    Jan 16, 2020

    Replace Apostrophe in SQL linked server query

    I need to search employee's last name from SQL server to DB2 database, one lastname like O'NEIL cause the error, here…

  • send email from c#
    Oct 25, 2019

    send email from c#

    using System.Net.

  • read email from active directory
    Sep 12, 2019

    read email from active directory

    private static string GetEmailAddressLDAP(string strEnv, string empno) { string strLDAPConnection; string strLDAPId;…

  • Multi-tenant Database design
    Sep 12, 2019

    Multi-tenant Database design

    There are 4 options to design Multi-tenant database: 1. One big database, one schema(dbo): In this approach, database…

  • Create Excel from List<T>
    Jul 19, 2019

    Create Excel from List<T>

    I need to generate the excel file by querying database everyday. All the data returned as List<Object>.

See all articles

Explore content categories