[SOLVED] mySQL GROUP BY

[mattl]

Hi,

I have a mySQL table, an extract is below.

I want to query to see if each distinct filename is either sold out or available. Each filename can have multiple ticket types, but the row returned for each filename should take into account the status for ALL ticket types for a filename.

 

Here's an example of what the GROUP BY would return (ie grouping by 'filename'):

Status Filename Concert Name Ticket Type

Sold Out 110708 Concert Name Adult

Available 110708 Concert Name Concession

Available 110708 Concert Name Child

Available 120708 Concert Name Adult

Sold Out 120708 Concert Name Concession

Sold Out 120708 Concert Name Child

etc...

 

I want the one row that is displayed on the page for each filename to show either 'Available' if ANY of the tickets are still available or 'Sold Out' if ALL the tickets are unavailable.

 

Can anyone suggest a way of getting this to work? I've tried using GROUP BY but that returns the first line it sees. There doesn't seem to be a way of sorting the returned group. Here's the query I have:

 

$query_events = "SELECT * FROM events WHERE `date` >= '2008' GROUP BY `filename` ORDER BY `date` ASC, `time` ASC, `status` ASC ";

 

Hope what I'm trying to do makes sense!!

 

Thanks

Matt

[sasa]

SELECT if(sum(if(Status='Available',1,0))>0,'Available','Sold Out') as sold,Filename FROM events WHERE `date` >= '2008' GROUP BY `filename` ORDER BY `date` ASC, `time` ASC, `status` ASC

[mattl]

Thanks, I tried this and it hasn't worked:

 

SELECT if(sum(if(status='Available',1,0))>0,'Available','Sold Out') as sold,date_display,time,event,status,filename,price FROM events_test WHERE `date` >= '2008' && `filename` NOT LIKE '%MinQP%' GROUP BY `filename` ORDER BY `date` ASC, `time` ASC

 

I have a line still returned showing 'Sold Out' when 1 of the 5 ticket types is still 'Available'. Any ideas.

 

Many thanks

Matt

[Barand]

SELECT DISTINCT e.filename, IF(x.filename IS NULL, 'Sold out', 'Available') as status

FROM events e

LEFT JOIN (SELECT DISTINCT filename FROM events WHERE status='Available') as x

ON e.filename = x.filename

[mattl]

thanks Barand,

How would I also return other columns from the query ie price, event, status etc and also filter all the results with a WHERE `date` >= '2008' && `filename` NOT LIKE '%MinQP%' ORDER BY `date` ASC, `time` ASC

 

 

 

Many thanks

 

Matt

[Barand]

I'm more comfortable with SQL when I can see the original data that is being queried. You should be able to add event to the selection (status is already included). It seems you have different prices though, so if you are only showing a single row, which price/ticket type would you show?

 

Add the WHERE clause also.

[mattl]

I need to display the following fields on the page for each row returned:

 

date_display, time, event, status (Do I add these in the first query as e.date_display etc or the 2nd query?)

 

I also return 'price' because I use a PHP showif to change text displayed if the price = 0. (ie some concerts are free - ticket price = 0)

 

Also, where in your example does the WHERE clause go?

Thanks

Matt

[Barand]

I suggest using a GROUP BY with MAX(price) in that case ( if the max price is 0 then it's free)

 

SELECT e.filename, e.date_display, e.time, e.event, MAX(price) as price
    IF(x.filename IS NULL, 'Sold out', 'Available') as status
FROM events e
LEFT JOIN (SELECT DISTINCT filename FROM events WHERE status='Available') as x
    ON e.filename = x.filename
WHERE `date` >= '2008' && `filename` NOT LIKE '%MinQP%'
GROUP BY e.filename 
ORDER BY `date` ASC, `time` ASC

[mattl]

I get the following error:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF(x.filename IS NULL, 'Sold Out', 'Available') as status FROM events_test e LEF' at line 1

 

Any ideas?

 

Matt

[Barand]

needs comma bfore the IF

[mattl]

Thanks,

Now get:

 

Column 'filename' in where clause is ambiguous

 

is this referring to the '`filename` NOT LIKE '%MinQP%''?

 

Matt

[Barand]

yes, make that e.filename

[mattl]

It bl**dy works!! Thanks so much Barand, I'd never have thought through that solution!

 

Here it is to close-off the discussion:

 

SELECT e.filename, e.date_display, e.time, e.event, MAX(price) as price, 
     IF(x.filename IS NULL, 'Sold Out', 'Available') as status 
FROM events_test e 
LEFT JOIN (SELECT DISTINCT filename FROM events_test WHERE status='Available') as x 
     ON e.filename = x.filename 
WHERE `date` >= '2008' && e.filename NOT LIKE '%MinQP%' 
GROUP BY e.filename 
ORDER BY `date` ASC, `time` ASC

 

Regards

Matt