• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 145
  • Last Modified:

I don't want to Group By!

I want to create a view in sql server that will give me the total number of hours worked based on the work center and a date range. When using the query designer it keeps forcing me to use Group By. I don't want to group my results. I want a single number. Please see attached image. The SQL Statement is:

SELECT     dbo.Job_Operation.Work_Center, SUM(dbo.Job_Operation_Time.Act_Run_Hrs) AS total, dbo.Job_Operation_Time.Work_Date
FROM         dbo.Job_Operation INNER JOIN
                      dbo.Job_Operation_Time ON dbo.Job_Operation.Job_Operation = dbo.Job_Operation_Time.Job_Operation
GROUP BY dbo.Job_Operation.Work_Center, dbo.Job_Operation_Time.Work_Date
HAVING      (dbo.Job_Operation_Time.Work_Date BETWEEN CONVERT(DATETIME, '2015-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2015-01-31 00:00:00', 102)) AND 
                      (dbo.Job_Operation.Work_Center = 'welding')

Open in new window

Capture.JPG
0
princeservice
Asked:
princeservice
  • 2
  • 2
  • 2
  • +1
1 Solution
 
David KrollCommented:
HAVING forces you to use a GROUP BY.  Change HAVING to WHERE.
0
 
princeserviceAuthor Commented:
I tried that. Still doesn't work. See image.

screen
0
 
Phillip BurtonCommented:
and change

dbo.Job_Operation_Time.Work_Date BETWEEN CONVERT(DATETIME, '2015-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2015-01-31 00:00:00', 102)

to

dbo.Job_Operation_Time.Work_Date >='20150101' and dbo.Job_Operation_Time.Work_Date <'20150201'
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
Phillip BurtonCommented:
It's because you have a SUM. You have to have a GROUP BY because you have a  SUM or other summarize function.

Why not do:

SELECT     SUM(dbo.Job_Operation_Time.Act_Run_Hrs) AS total
FROM         dbo.Job_Operation INNER JOIN
                      dbo.Job_Operation_Time ON dbo.Job_Operation.Job_Operation = dbo.Job_Operation_Time.Job_Operation
WHERE dbo.Job_Operation_Time.Work_Date BETWEEN CONVERT(DATETIME, '2015-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2015-01-31 00:00:00', 102) AND (dbo.Job_Operation.Work_Center = 'welding')

Open in new window


if you just want a single figure?
0
 
ste5anSenior DeveloperCommented:
Then don't rely on wizards and write your statements by hand. E.g.

SELECT  SUM(JOT.Act_Run_Hrs) AS total
FROM    dbo.Job_Operation JO
        INNER JOIN dbo.Job_Operation_Time JOT ON JO.Job_Operation = JOT.Job_Operation
WHERE   JOT.Work_Date BETWEEN '20150101' AND '20150131 '
        AND JO.Work_Center = 'welding' ;

Open in new window

0
 
David KrollCommented:
SELECT     dbo.Job_Operation.Work_Center, 
(SELECT SUM(dbo.Job_Operation_Time.Act_Run_Hrs) 
 FROM dbo.Job_Operation_Time 
 WHERE  dbo.Job_Operation_Time.Job_Operation = dbo.Job_Operation.Job_Operation
 AND dbo.Job_Operation_Time.Work_Date BETWEEN CONVERT(DATETIME, '2015-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2015-01-31 00:00:00', 102)
) AS total, dbo.Job_Operation_Time.Work_Date
FROM   dbo.Job_Operation 
WHERE   (dbo.Job_Operation.Work_Center = 'welding')

Open in new window

0
 
princeserviceAuthor Commented:
ste5an - How do I create a view without using the wizard?
0
 
ste5anSenior DeveloperCommented:
E.g.

CREATE VIEW yourViewName
AS
SELECT  SUM(JOT.Act_Run_Hrs) AS total
FROM    dbo.Job_Operation JO
        INNER JOIN dbo.Job_Operation_Time JOT ON JO.Job_Operation = JOT.Job_Operation
WHERE   JOT.Work_Date BETWEEN '20150101' AND '20150131 '
        AND JO.Work_Center = 'welding' ;

Open in new window

0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now