CODEWITHSUNDEEP
phpsqlsyntaxsyntax-error
Matt Sherer
Matt Sherer

Reputation: 13

SQL statement not producing any results

SELECT 
    ID AS Employee_Name,
    department AS Employee_Department,
    business_unit AS Employee_Business,
    SUM(hours_used) Employee_Hours
FROM 
    TableX
WHERE
    Day_Used BETWEEN 04/01/2014 AND 04/30/2014
GROUP BY
    department

This is my current SQL statement, and what I'm trying to do is get output from the database however I have not been successful and maybe you guys can point out why.

The goal is to SUM all employee hours by department, and group them by department between the given dates. I know my statement would not display that information as of right now, I am currently just trying to figure out why I can't get any output.

Thanks for your time!

EDIT: Here is a simpler test query that works with the SUM(hours_used) working correctly.

   SELECT
       ID AS Employee_Name,
       SUM(hours_used) AS Employee_Hours
   FROM
       TableX
   WHERE
       Day_Used BETWEEN 04/01/2014 AND 04/30/2014
   GROUP BY
       ID

I am very grateful for all of your feedback! This is what my original SQL problem looks like so far. I just need to figure out how to get it to SUM(hours_used) by department. (IE: Ted has 15 hours for accounting and 10 hours for companyX)

SELECT 
    ID AS Employee_Name,
    department AS Employee_Department,
    business_unit AS Employee_Business,
    SUM(hours_used) AS Employee_Hours
FROM 
    TableX
WHERE
    Day_Used BETWEEN '04/01/2014' AND '04/30/2014'
GROUP BY
    ID, department, business_unit, hours_used

Pecci inadvertently solved my last problem! I just had to remove hours_used in my GROUP BY and everything displayed as it should! I'd like to give a HUGE thank you to everyone who helped solve my problem, thanks so much!!!

Upvotes: 1

Views: 84

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

The primary problem are the date constants:

SELECT ID AS Employee_Name,
       department AS Employee_Department,
       business_unit AS Employee_Business,
       SUM(hours_used) as Employee_Hours
FROM TableX
WHERE Day_Used BETWEEN '2014-04-01' AND '2014-04-30'
GROUP BY id, department, business_unit;

Most databases will recognize the ISO standard YYYY-MM-DD format for date constants. Your specific database might have another preferred format, but the constant value does need to be between single quotes.

Upvotes: 0

Elias
Elias

Reputation: 2632

You don't have all of your fields in an aggregate function or the group by statement.

SELECT 
    ID AS Employee_Name,
    department AS Employee_Department,
    business_unit AS Employee_Business,
    SUM(hours_used) Employee_Hours
FROM 
    TableX
WHERE
    Day_Used BETWEEN '04/01/2014' AND '04/30/2014'
GROUP BY
    ID,department,business_unit

The reason for adding the other columns as an aggregate (SUM, COUNT, ETC) or to the GROUP BY is because of how the data is being looked at.

Say you have the following table

ID   intValue  Color
1    1         Blue
2    2         Blue
3    5         Green
4    10        Red
5    15        Red
6    5         Blue

And I want to get a SUM of of all of the intValue by color.

I would want to first make sure to select both columns SELECT intValue, Color FROM table1

Now, since I want the sum of the intValue, I would add the aggregate function SELECT SUM(intValue), Color FROM table1

Now, the issue you're having is here. Without telling the DBMS what you want to group by (The colors in this example) how else would it know? By adding a group by I tell it that I want to have the SUM of all intValue's where it has been grouped on Color. SELECT SUM(intValue), Color FROM table

By not doing either an aggregate or a group by on all of the column, the DBMS has no idea what to do with your query.

Upvotes: 1

pecci
pecci

Reputation: 540

When you use SUM, COUNT... you need to put all the others columns in the group by. Another thing to look is how the dates are stored in the db and if your are using the between correctly.

Upvotes: 0

Related Questions