-1

I have the following table:

Source

I want to get this table:

Destination

I want to get all COL1 having at least 2 distinct COL2 values and to display in the COL2 of the result the distinct COL2 values with the count of all the corresponding COL3 in ().

Thanks in advance for your help.

I am expecting the more optimized SQL query to get the result.

Yann
  • 1
  • Which dbms are you using? (string_agg is a product specific feature.) – jarlh Jul 27 '23 at 08:48
  • 1
    Most people here want sample table data and expected result as properly formatted text (i.e. no images, no links.) Also show us your current SQL attempt. – jarlh Jul 27 '23 at 08:49
  • [why-should-i-not-upload-images-of-code-data-errors](https://meta.stackoverflow.com/questions/285551/why-should-i-not-upload-images-of-code-data-errors) – Stu Jul 27 '23 at 08:57
  • Needs more info for a proper solve as its not completely clear what you're looking to achieve. You should be able to solve this with a GROUP BY having clause to set up a data set to evaluate. Then perform the counts and a simple concat depending on your structure. – Connor Willoughby Jul 27 '23 at 12:48
  • Please provide enough code so others can better understand or reproduce the problem. – Community Jul 27 '23 at 15:46

1 Answers1

0

Source:

COL1 ; COL2 ; COL3

A ; Y ; 1

A ; Y ; 1

A ; Z ; 3

B ; Y ; 2

B ; Y ; 2

D ; Y ; 3

D ; Z ; 2

Result

COL1 ; COL2

A ; (Y:2;Z:3)

D ; (Y:3;Z:2)

select COL1,'('+ COL2 + ':' + sum(COL3) + ')'

from table

group by COL1,COL2

having count(COL2)>1

order by 1

Yann
  • 1
  • As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Aug 03 '23 at 05:51