Group by query unless column=1

[biggieuk]

Hi all,

 

I have a SQL statement which selects all records from a table, groups them by product_code, product_colour & product_size. I have added a new column 'admin'. I need to alter the query to return the items like before but only group the items that have 'admin' set to 0.

 

Is this possible in one single query?

 

SELECT *, SUM(quantity) as item_quantity
FROM orders_items
WHERE orders_id="1" 
GROUP BY  code, sizes_id, sizes_id_2, colours_id
ORDER BY id ASC

 

Thanks for help with this

[kickstart]

Hi

 

Bit uncertain what you want to count.

 

You could just add the admin column to the group by. This would give you a row for admin = 0 and a row for admin = 1 for each set of data which contained both.

 

You could do something like this:-

 

SELECT *, SUM(IF(admin = 1, 0, quantity)) as item_quantity
FROM orders_items
WHERE orders_id="1" 
GROUP BY  code, sizes_id, sizes_id_2, colours_id
ORDER BY id ASC

 

All the best

 

Keith

[biggieuk]

thanks for your reply, I think my explanation could have been better.

 

Basically I am currently returning rows like so:

 

code

colour

size

quantity

admin

123

2

34

1

0

143

6

23

3

0

145

8

37

1

0

 

The query is grouping the rows by code, size and colour then counting the quantity column.

 

I need the query to group the results like above but only if the admin column is 0.  If admin is 1 then just output the row without grouping  like so:

 

code

colour

size

quantity

admin

123

2

34

1

0

143

6

23

1

1

143

6

23

1

1

143

6

23

1

1

145

8

37

1

0

 

 

Is that any clearer?

[kickstart]

Hi

 

It is, and not sure there is an elegant way to do it.

 

Best way I can think would be 2 queries UNIONed together, one for the admin and one for the non admin rows.

 

Something like (please excuse any typos):-

 

SELECT *, SUM(quantity) as item_quantity
FROM orders_items
WHERE orders_id="1" 
AND admin = 0
GROUP BY  code, sizes_id, sizes_id_2, colours_id
UNION ALL
SELECT *, quantity as item_quantity
FROM orders_items
WHERE orders_id="1" 
AND admin = 1

 

All the best

 

Keith

 

[biggieuk]

Thanks Keith,

 

I managed to fit your example to my project