2

I'm having trouble running/configuring a query for Microsoft SQL Server, the query is as follows: 

SELECT
    ps.WeekIncluded AS PaidWeeks,
    PayD.PayDate AS PayDates,
    ps.PayYear AS PYear, 
    months.PMonth,
    (SELECT sum(DayPay) FROM Shifts GROUP BY WeekNumber)
FROM dbo.PayStructure ps
JOIN dbo.Months Months 
ON ps.MonthID = months.ID
JOIN dbo.PayDates PayD ON ps.MonthID = PayD.MonthID
Group BY ps.MonthID

What this is trying to do, is create a view (not included in snippet) using three tables, including selecting the sum of DayPay in Shifts and GroupBy the week number to be later joined and joined by a weeknumber to the specified month. Unfortunately i'm getting:

Column 'dbo.PayStructure.WeekIncluded' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

& using: 

SELECT
    ps.WeekIncluded AS PaidWeeks,
    PayD.PayDate AS PayDates,
    ps.PayYear AS PYear, 
    months.PMonth,
    (SELECT sum(DayPay) FROM Shifts)
FROM dbo.PayStructure ps
JOIN dbo.Months Months 
ON ps.MonthID = months.ID
JOIN dbo.PayDates PayD ON ps.MonthID = PayD.MonthID

Returns:

Sucessful Query, Wrong results

Every selected is equal to: 60.36 where what i'm trying to get is:

Janurary - Null

February - Null

March - Null

April - Null

May - Null

June - Null

July - Null

August - Null

September - 60.36

October - Null

December - Null

Null being for 11/12 months due to having no data input for those week numbers/months

As Asked for. Dbo.Shifts Dbo.shifts

Dbo.PayStructure dbo.PayStructure

Dbo.Months Dbo.Months

Attempted:

    SELECT
     ps.WeekIncluded AS PaidWeeks, 
     PayD.PayDate AS PayDates,
     ps.PayYear AS PYear,
       SUM(Sh.DayPay) 
FROM   dbo.PayStructure ps
       LEFT JOIN dbo.Months Months 
         ON ps.MonthID = months.ID
       LEFT JOIN dbo.PayDates PayD
         ON ps.MonthID = PayD.MonthID
        LEFT JOIN dbo.Shifts Sh
         ON Sh.WeekNumber = ps.WeekIncluded
GROUP  BY Sh.WeekNumber
Improve this question
2
  • can you post the sample input tables with some rows ? Commented Sep 1, 2014 at 23:29
  • @BoratSagdiyev Please see the update for Screenshots of the referenced tables Commented Sep 1, 2014 at 23:33

3 Answers 3

Reset to default
2 
SELECT
ps.WeekIncluded AS PaidWeeks,
PayD.PayDate AS PayDates,
ps.PayYear AS PYear, 
months.PMonth,
SumPay
FROM dbo.PayStructure ps
JOIN dbo.Months Months 
ON ps.MonthID = months.ID
JOIN dbo.PayDates PayD ON ps.MonthID = PayD.MonthID
LEFT JOIN 
(SELECT S. WeekNumber,
            PSI.MonthId,
            PSI.PayYear,
            sum(DayPay) AS SumPay
FROM Shifts S
JOIN dbo.PayStrycture PSI
ON S.WeekNumber = PSI.WeekIncluded
AND YEAR(S.ShiftDate) = PSI.PayYear
JOIN dbo.Months M
ON PSI.MonthId = M.ID
GROUP BY S. WeekNumber,
            PSI.MonthId,
            PSI.PayYear
) T
ON PS.MonthId = T.MonthId
AND PS.PayYear = T.PayYear
AND PS.WeekIncluded = T.WeekNumber
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

This works like an absolute Charm. Though, if it's not too much trouble. Could you walk through the changes made from the original query?
@darylgill You have to think as having two separate tables. The first one is the one that has all the weeks and months and years possible. Try my query removing whatever is from the LEFT JOIN and after(also remove SumDay from the column list) and see these results.
Then run whatever is within the select defined after the LEFT JOIN. This is your actual calculated data. Next step is to combine the possible timetable with the calculated data. I hope i did a good job explaining....
I completely follow what's going on with this query. Though i've hit another problem which i'm trying to figure out myself, but a little out of the zone. Group by Pmonth by concatenate PaidWeeks
1

The error message is fairly clear; SQL Server cannot know from your query which possible value of WeekIncluded it should include the result set for you query. Think of it yourself -- there will be one row per month in your output and one "slot" for a WeekIncluded value. Which one should be shown?

Try running the query like this:

SELECT
    MIN(ps.WeekIncluded) AS FromWeek,
    MAX(ps.WeekIncluded) AS ToWeek,
    . . .

and you should get a better idea of what's going on.

A couple of other engines include an extra aggregate function called group_concat to handle this kind of query but SQL Server makes it pretty hard to roll-up a comma-separated list of values in an aggregate like this.

Improve this answer

2 Comments

The min/max returns 1 - 52 which is expected and known, due to 52 weeks in the year. Will look into concatenation of strings as it seems that might be a cause of the problem
Well, something's wrong already. The MIN/MAX values for MonthID 1, according to the data you posted, should be 37 and 40. I guess, actually, MonthID 4 will show values of 1 and 52 because of the way the WeekIncluded value wraps around. To fit them into the "right" slots you'd need to compare the absolute value of the difference and, if greater than 5, reverse the values.
0

Any SELECTed columns that aren't aggregates (e.g. SUM() or COUNT()) will have to be included in the GroupBy or the query won't execute.

So:

SELECT
ps.WeekIncluded AS PaidWeeks,
PayD.PayDate AS PayDates,
ps.PayYear AS PYear, 
months.PMonth,
(SELECT sum(DayPay) FROM Shifts GROUP BY WeekNumber)
FROM dbo.PayStructure ps
JOIN dbo.Months Months 
ON ps.MonthID = months.ID
JOIN dbo.PayDates PayD ON ps.MonthID = PayD.MonthID
Group BY ps.MonthID,ps.WeekIncluded,PayD.PayDate,months.PMonth
Improve this answer

1 Comment

I think the user clearly wants one output record per month. The solution is not to GROUP BY WeekIncluded but rather to aggregate it in some meaningful way.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.