1

If I want to know the total amount of salary on each customer, then GROUP BY query would be as follows:

SELECT NAME, SUM(SALARY) FROM CUSTOMERS GROUP BY NAME;

But, if I don't want sum of salaries and just need to look all salaries grouped by name then what will be query or approach?

Paul Spiegel
  • 30,925
  • 5
  • 44
  • 53
Shobhit Singh
  • 11
  • 1

3 Answers3

1

That depends on which salary you want to show..

Option 1 -> maximum:

SELECT NAME, max(SALARY) FROM CUSTOMERS GROUP BY NAME;

Option 2 -> minimum:

SELECT NAME, min(SALARY) FROM CUSTOMERS GROUP BY NAME;

Option 3 -> list of them:

SELECT NAME, group_concat(SALARY separator ',') FROM CUSTOMERS GROUP BY NAME;

And last option -> doesn't matter :

SELECT NAME,SALARY FROM CUSTOMERS GROUP BY NAME;
sagi
  • 40,026
  • 6
  • 59
  • 84
  • 1
    One more option: group on application site instead of SQL. – Paul Spiegel Mar 28 '16 at 12:56
  • One more option on how to do it, I talked about all the options that he could want. @PaulSpiegel – sagi Mar 28 '16 at 12:59
  • Maybe OP just doesn't know what's possible in his application language. For example with PHP PDO you can use something like `fetchAll(PDO::FETCH_COLUMN|PDO::FETCH_GROUP)` and keep the result more flexible, because it's an array. On the other hand it's also simple to parse the resulting string - so everything is fine :-) – Paul Spiegel Mar 28 '16 at 13:18
  • @sagi group_concat is what I was looking for but in JPA query group_concat not works. Any idea what I can do in JPA query for group_concat? – Shobhit Singh Mar 29 '16 at 09:25
  • @ShobhitSingh You can find what you need here : http://stackoverflow.com/questions/7005354/jpa-criteria-api-group-concat-usage – sagi Mar 29 '16 at 09:41
  • @sagi in this post criteriaquery is using but i am not using that. I just create jpa query using stringbuilder and use createQuery method of entity manager for getting query object. Does any built-in function exists is like group_concat and which also allowed in JPA query? – Shobhit Singh Mar 29 '16 at 14:55
0

Try to use GROUP BY clause for getting desired results.

SELECT NAME,SALARY FROM CUSTOMERS GROUP BY NAME;
Muhammad Arif
  • 1,014
  • 3
  • 22
  • 56
0

If you want to retrieve name and variation of salary for each customers (grouped by name and salary). You can try this.

SELECT name, salary FROM customers GROUP BY name, salary;
Jopie
  • 336
  • 3
  • 9