none
Get many columns with one group by column RRS feed

  • Question

  • Sir,

    I have these codes

    USE at18;
    IF OBJECT_ID('tempdb.dbo.#Table1', 'U') IS NOT NULL
      DROP TABLE #Table1; 
     
    CREATE TABLE #Table1
    (
    code VARCHAR(50),
    	names VARCHAR(50),
    	country VARCHAR(50),
    	qty int
    )
    
    INSERT INTO #table1 VALUES (1104,'ERIC','Holland',45)
    INSERT INTO #table1 VALUES (1105,'Boris','Bulgaria',50)
    INSERT INTO #table1 VALUES (1106,'Anil','India',75)
    INSERT INTO #table1 VALUES (1107,'Cetin','Turkey',65)
    INSERT INTO #table1 VALUES (1105,'Boris','Bulgaria',40)
    INSERT INTO #table1 VALUES (1104,'ERIC','Holland',65)
    INSERT INTO #table1 VALUES (1107,'Cetin','Turkey',85)
    INSERT INTO #table1 VALUES (1106,'Anil','India',25)
    
    select * from #table1 order by code
    
    select code,max(names)as names,max(country)as country,sum(qty)as qty 
    from #Table1
    group by code
    order by code

    The codes work fine like shown in this image

    I used max() clause to get all other columns except column that I used in group by.

    Is there any other method to get above result without using max() clause?

    I mean ;With CTE or Join

    Please help


    tqmd

    Saturday, September 14, 2019 5:19 AM

Answers

  • Get many columns with one group by column

    If you are sure that for each value in the "code" column the values in the "names" and "country" columns are the same, then here is a suggestion:

    -- code #1 v2
    with aggCode as (
    SELECT code, sum (qty) as qty
      from #Table1
      group by code
    )
    SELECT A.code, T.names, T.country, A.qty
    from aggCode as A
    cross apply (SELECT top (1) T1.names, T1.country
    from #Table1 as T1
    where T1.code = A.code) as T
    order by A.code;
     

    or

    -- code #3
    with 
    aggCode as (
    SELECT code, sum (qty) as qty 
      from #Table1
      group by code
    ),
    uniqCode as (
    SELECT distinct code, names, country 
      from #Table1
    )
    SELECT A.code, U.names, U.country, A.qty
      from aggCode as A
           inner join uniqCode as U on U.code = A.code
      order by A.code;

    ---

    But why don't you use the traditional approach?

    -- code #2
    SELECT code, names, country, sum (qty) as qty 
      from #Table1
      group by code, names, country
      order by code;



    José Diz     Belo Horizonte, MG - Brasil     [T-SQL performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    • Marked as answer by Tariq Mehmood Sunday, September 15, 2019 5:16 AM
    • Edited by José Diz Sunday, September 15, 2019 10:01 AM
    Saturday, September 14, 2019 12:11 PM
  • Hi Tariq

    As per the posted need below query is one of the simplest mode of doing it in the below manner. Please share if you have any specific necessity of expecting the query results through/using CTE or Joins.

    select code,names,country,sum(qty)as qty 
    from #Table1
    GROUP BY code,names,country
    order by 1

    Hope this is helpful!!

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    • Marked as answer by Tariq Mehmood Sunday, September 15, 2019 5:16 AM
    Saturday, September 14, 2019 1:38 PM

All replies

  • Get many columns with one group by column

    If you are sure that for each value in the "code" column the values in the "names" and "country" columns are the same, then here is a suggestion:

    -- code #1 v2
    with aggCode as (
    SELECT code, sum (qty) as qty
      from #Table1
      group by code
    )
    SELECT A.code, T.names, T.country, A.qty
    from aggCode as A
    cross apply (SELECT top (1) T1.names, T1.country
    from #Table1 as T1
    where T1.code = A.code) as T
    order by A.code;
     

    or

    -- code #3
    with 
    aggCode as (
    SELECT code, sum (qty) as qty 
      from #Table1
      group by code
    ),
    uniqCode as (
    SELECT distinct code, names, country 
      from #Table1
    )
    SELECT A.code, U.names, U.country, A.qty
      from aggCode as A
           inner join uniqCode as U on U.code = A.code
      order by A.code;

    ---

    But why don't you use the traditional approach?

    -- code #2
    SELECT code, names, country, sum (qty) as qty 
      from #Table1
      group by code, names, country
      order by code;



    José Diz     Belo Horizonte, MG - Brasil     [T-SQL performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    • Marked as answer by Tariq Mehmood Sunday, September 15, 2019 5:16 AM
    • Edited by José Diz Sunday, September 15, 2019 10:01 AM
    Saturday, September 14, 2019 12:11 PM
  • Hi Tariq

    As per the posted need below query is one of the simplest mode of doing it in the below manner. Please share if you have any specific necessity of expecting the query results through/using CTE or Joins.

    select code,names,country,sum(qty)as qty 
    from #Table1
    GROUP BY code,names,country
    order by 1

    Hope this is helpful!!

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    • Marked as answer by Tariq Mehmood Sunday, September 15, 2019 5:16 AM
    Saturday, September 14, 2019 1:38 PM