2

I want to Apply condition for GROUP BY.

When the condition city_id != 0 is true, group the list. Otherwise normal list.

I used this query for that:

(
    SELECT city_id, sum(sales) as counts
    FROM product_sales
    WHERE city_id !=0     
    GROUP BY city_id
)
UNION
(
    SELECT city_id, sales 
    FROM product_sales  
    WHERE city_id =0     
    ORDER BY sales_id 
)

Anyone can help me avoid the UNION and get the list in a single query?

Cyril Gandon
  • 16,830
  • 14
  • 78
  • 122
Sino Thomas
  • 141
  • 1
  • 7
  • 22

1 Answers1

4

One idea : GROUP BY the city_id when it is not zero, else emulate a random unique value for grouping with UUID(). So each row with city_id = 0 will not be grouped.

select city_id, sum(sales)
from product_sales
group by
  case when city_id = 0
  then UUID()
  else city_id
  end

SQL Fiddle.

A UUID is designed as a number that is globally unique in space and time. Two calls to UUID() are expected to generate two different values, even if these calls are performed on two separate computers that are not connected to each other.

Cyril Gandon
  • 16,830
  • 14
  • 78
  • 122
  • `when city_id = 0` should be `when city_id <> 0` – hjpotter92 Apr 16 '13 at 08:14
  • @hjpotter92 : are you sure? I re-read the question and I'm pretty sure the condition is right. – Cyril Gandon Apr 16 '13 at 08:18
  • SELECT city_id, sum(sales) as counts FROM product_sales WHERE city_id !=0 – hjpotter92 Apr 16 '13 at 08:21
  • If the city is different from 0, then we group by city_id, it is what the OP ask. – Cyril Gandon Apr 16 '13 at 08:24
  • This answer seems to be my solution but is there some drawbacks using UUID like this??? – developer34 May 17 '16 at 12:29
  • you can look [here](http://stackoverflow.com/questions/45399/advantages-and-disadvantages-of-guid-uuid-database-keys) or [here](http://stackoverflow.com/questions/5949/whats-your-opinion-on-using-uuids-as-database-row-identifiers-particularly-in). It doesn't exactly talk about using it for random sorting, but it still has interesting point. – Cyril Gandon May 17 '16 at 14:31