Multiple SQL Select and Where on Same Table

Question

I need to make a query on one table in a MYSQL database. In this query I need to make multiple selects with different where's.

My current query is:

$result = mysql_query("
    SELECT
        last_name, first_name, user_name,
        SUM(rthours) as rthours, SUM(othours) as others,
        SUM(trthours) as trthours, SUM(tothours) as tot hours
    FROM data
    WHERE labor_date BETWEEN '$start' AND '$end'
    ORDER BY last_name ASC
");

I also need to select the following two:

SUM(rthours) as vhours FROM data WHERE decription = 'vacation' AND labor_date BETWEEN '$start' AND '$end'

and

SUM(rthours) as shours FROM data WHERE decription = 'sick' AND labor_date BETWEEN '$start' AND '$end'

How can I combine all three selects into one query so that I can then use them in a table that I am outputting afterwards.

Thanks for any help!

M Khalid Junaid · Accepted Answer · 2014-02-11 20:07:26Z

1

You can use CASE within SUM()

SELECT last_name, first_name, user_name,
SUM( CASE WHEN decription = 'vacation' AND labor_date BETWEEN '$start' AND '$end' THEN rthours ELSE 0 END) as rthours,
SUM(CASE WHEN decription = 'sick' AND labor_date BETWEEN '$start' AND '$end' THEN rthours  ELSE 0 END) as shours,    
SUM(othours) as othours, 
SUM(trthours) as trthours, 
SUM(tothours) as tothours 
FROM data 
WHERE labor_date BETWEEN '$start' AND '$end' 
/*missing group by*/
Order by last_name ASC

For aggregate function you should use the group function

answered Feb 11, 2014 at 20:07

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Lawrence Over a year ago

This returns no records when used. It should as there is data in the table that meets the requirements of the select statement.

Lawrence Over a year ago

After some research I edited the above code to this:

$result = mysql_query("SELECT last_name, first_name, SUM(CASE WHEN decription = 'Vacation' THEN rthours ELSE 0 END) as vhours, SUM(CASE WHEN decription = 'Sick' THEN rthours  ELSE 0 END) as shours,     SUM(othours) as othours, SUM(trthours) as trthours,  SUM(tothours) as tothours  FROM data  WHERE labor_date BETWEEN '$start' AND '$end' Order by last_name ASC");

but it still does not return any results.

M Khalid Junaid Over a year ago

@Lawrence can you add your tables with sample dataset on sqlfiddle.com then it will be easier to identify

Fabien Warniez · Accepted Answer · 2014-02-11 20:07:12Z

0

You can use a UNION ALL. It combines the results of several queries into one result set.

Look it up: http://dev.mysql.com/doc/refman/5.0/en/union.html

answered Feb 11, 2014 at 20:07

Fabien Warniez

2,7411 gold badge21 silver badges30 bronze badges

Comments

Useless Intern · Accepted Answer · 2014-02-11 20:10:09Z

0

It will be a mess but you can can run those as subqueries:

SELECT last_name, first_name, user_name, SUM(rthours) as rthours,
SUM(othours) as othours, SUM(trthours) as trthours, 
SUM(tothours) as tothours, 
(SUM(rthours) as vhours FROM data WHERE decription = 'vacation' AND labor_date BETWEEN '$start' AND '$end') AS vhours
(SUM(rthours) as shours FROM data WHERE decription = 'sick' AND labor_date BETWEEN '$start' AND '$end') AS shours
FROM data WHERE labor_date BETWEEN '$start' AND '$end' Order by last_name ASC

I can't run these since I dont have a table or data but its the basic idea that matters

answered Feb 11, 2014 at 20:10

Useless Intern

1,3021 gold badge10 silver badges21 bronze badges

1 Comment

Lawrence Over a year ago

This returns no records when used. It should as there is data in the table that meets the requirements of the select statement.

Lawrence · Accepted Answer · 2014-02-12 14:57:29Z

0

Got it.

This works:

$result = mysql_query("SELECT last_name, first_name,
SUM(CASE WHEN description = 'Vacation' THEN rthours ELSE 0 END) as vhours,
SUM(CASE WHEN description = 'Sick' THEN rthours ELSE 0 END) as shours,   
SUM(rthours) as rthours,
SUM(othours) as othours,
SUM(trthours) as trthours, 
SUM(tothours) as tothours
FROM data 
WHERE labor_date BETWEEN '$start' AND '$end' Group by last_name Order by last_name ASC");

Thanks for the help!!!!

answered Feb 12, 2014 at 14:57

Lawrence

412 silver badges8 bronze badges

Collectives™ on Stack Overflow

Multiple SQL Select and Where on Same Table

4 Answers 4

3 Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related