Start Free Trial

asked on

SQL Count issue

Hi I am using this SQL statement to get the information shown in the diagram just below. I am trying to also get the number of Machine Numbers that appear in this result. So for example at the top of the list you can see that there are 2 machines for the Shaft "bambanani air". How do I expand the code so that I have an additional column that returns this number?

SELECT count(DISTINCT concat([Date], [Shift])) As [Shift Count],Count([Machine Number]) As [Machine Count], Sum([Drilled Total]) as [Total Drilled], [Shaft]
FROM [Performance]
Where  [Date] >= Date1 And [Date] <= Date2
Group By [Machine Number],[Shaft]

Open in new window

User generated image

Vitor Montalvão

Remove the COUNT for the machine number:

SELECT count(DISTINCT concat([Date], [Shift])) As [Shift Count],
	[Machine Number], 
	Sum([Drilled Total]) as [Total Drilled], 
	[Shaft]
FROM [Performance]
Where  [Date] >= Date1 And [Date] <= Date2
Group By [Machine Number],[Shaft]

Open in new window

ASKER

Hi. Thanks. I did that but am still not sure how to get the number of machines in the list. Do I maybe need to do a Select on this Select statement

Vitor Montalvão

Your original select should return the number of machines. The one I provided returns the total by machine.
Can you provide a sample of the desired result so I understand better your requirement?

ASKER

Hi
The following image shows the last column as a sample of what I need. It is a count of how many machine numbers there are per shaft.

User generated image

ASKER CERTIFIED SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Hi

I am probably not explaining myself properly. What I need is the actual number of machines per shaft that appear
in this query as shown by the yellow and blue examples that I did in Excel. The reason I need this is that I am trying to calculate
the meters drilled per machine per shift

User generated image

Vitor Montalvão

Yes, you did explain good. It's just because I don't have any data for test.
Please try this more complex version and let me know of the outcome:

;WITH CTE_Machine
AS (
	SELECT count(DISTINCT concat([Date], [Shift])) As [Shift Count],
		[Machine Number], 
		Sum([Drilled Total]) as [Total Drilled], 
		[Shaft]
	FROM [Performance]
	Where  [Date] >= Date1 And [Date] <= Date2
	Group By [Machine Number],[Shaft]
	)
SELECT CTE_Machine.[Shift Count], CTE_Machine.[Machine Number], CTE_Machine.[Total Drilled], CTE_Machine.[Shaft], Machines.[Machines per Shaft]
FROM CTE_Machine
	INNER JOIN 
		(SELECT [Shaft], COUNT([Machine Number]) [Machines per Shaft]
		FROM [Performance]
		Where [Date] >= Date1 And [Date] <= Date2
		Group By [Shaft]) AS Machines ON CTE_Machine.[Shaft] = Machines.[Shaft]

Open in new window

ASKER

Thanks. Here is the result

User generated image

Vitor Montalvão

Looks like I missed a join with Shift Count:

;WITH CTE_Machine
AS (
	SELECT count(DISTINCT concat([Date], [Shift])) As [Shift Count],
		[Machine Number], 
		Sum([Drilled Total]) as [Total Drilled], 
		[Shaft]
	FROM [Performance]
	Where  [Date] >= Date1 And [Date] <= Date2
	Group By [Machine Number],[Shaft]
	)
SELECT CTE_Machine.[Shift Count], CTE_Machine.[Machine Number], CTE_Machine.[Total Drilled], CTE_Machine.[Shaft], Machines.[Machines per Shaft]
FROM CTE_Machine
	INNER JOIN 
		(SELECT [Shaft], COUNT([Machine Number]) [Machines per Shaft]
		FROM [Performance]
		Where [Date] >= Date1 And [Date] <= Date2
		Group By [Shaft]
		) AS Machines ON CTE_Machine.[Shaft] = Machines.[Shaft] AND CTE_Machine.[Shift Count] = concat(Machines.[Date], Machines.[Shift])

Open in new window

ASKER

Unfortunately that still isn't right. Is there not maybe a way to do a Select on a Select statement. eg Select * From (Select * From...)

ASKER

Ok will test

ASKER

I get an error. Incorrect syntax near ")"

Vitor Montalvão

I couldn't see any extra parenthesis but I saw a flaw in my query and fixed it:

;WITH CTE_Machine
AS (
	SELECT count(DISTINCT concat([Date], [Shift])) As [Shift Count],
		[Machine Number], 
		Sum([Drilled Total]) as [Total Drilled], 
		[Shaft]
	FROM [Performance]
	Where  [Date] >= Date1 And [Date] <= Date2
	Group By [Machine Number],[Shaft]
	)
SELECT CTE_Machine.[Shift Count], CTE_Machine.[Machine Number], CTE_Machine.[Total Drilled], CTE_Machine.[Shaft], Machines.[Machines per Shaft]
FROM CTE_Machine
	INNER JOIN 
		(
		SELECT [Shaft], count(DISTINCT concat([Date], [Shift])) As [Shift Count], COUNT([Machine Number]) [Machines per Shaft]
		FROM [Performance]
		Where [Date] >= Date1 And [Date] <= Date2
		Group By [Shaft]
		) AS Machines ON CTE_Machine.[Shaft] = Machines.[Shaft] AND CTE_Machine.[Shift Count] = Machines.[Shift Count]

Open in new window

ASKER

Hi. This is the result

User generated image

ASKER

Let me try and have a closer look at your query to see if I can work things out

Vitor Montalvão

Or give me some sample data so I can test my queries.

ASKER

I tried this sort of approach which I think will work once complete

Select a.[Shift Count], Count(a.Shaft) As SCount, a.[Total Drilled], a.[Shaft] From (SELECT count(DISTINCT concat([Date], [Shift])) As [Shift Count],Count([Machine Number]) As [Machine Count], Sum([Drilled Total]) as [Total Drilled], [Shaft] 
FROM [Performance]
Where  [Date] >= Date1 And [Date] <= Date2
Group By [Machine Number],[Shaft]) a
Group By a.[Shift Count],a.[Total Drilled],a.[Shaft]

Open in new window

ASKER

Thanks Vitor. I will accept this answer as it really helped

Vitor Montalvão

Hummm...
I might went to a more complex path.
Cheers

ASKER

Thanks Vitor. I appreciate your help as always

ASKER

Hi Vitor. I made a few tweaks to your final SQL statement and got the result I needed. So thanks very much for this

WITH CTE_Machine
AS (
	SELECT count(DISTINCT concat([Date], [Shift])) As [Shift Count],
		[Machine Number], 
		Sum([Drilled Total]) as [Total Drilled], 
		[Shaft]
	FROM [Performance]
	Where  [Date] >= Date1 And [Date] <= Date2
	Group By [Machine Number],[Shaft]
	)
SELECT CTE_Machine.[Shift Count], CTE_Machine.[Machine Number], CTE_Machine.[Total Drilled], CTE_Machine.[Shaft], Machines.[Machines per Shaft]
FROM CTE_Machine
	INNER JOIN 
		(
		SELECT [Shaft], count(DISTINCT concat([Date], [Shift])) As [Shift Count], COUNT(Distinct [Machine Number]) [Machines per Shaft]
		FROM [Performance]
		Where [Date] >= Date1 And [Date] <= Date2
		Group By [Shaft]
		) AS Machines ON CTE_Machine.[Shaft] = Machines.[Shaft]

Open in new window

User generated image

ASKER

Here's the final result that I needed. Thanks again for all the help

WITH CTE_Machine
AS (
	SELECT count(DISTINCT concat([Date], [Shift])) As [Shift Count],
		[Machine Number], 
		Sum([Drilled Total]) as [Total Drilled], 
		[Shaft]
	FROM [Performance]
	Where  [Date] >= Date1 And [Date] <= Date2
	Group By [Machine Number],[Shaft]
	)
SELECT CTE_Machine.[Shift Count], CTE_Machine.[Machine Number], CTE_Machine.[Total Drilled], CTE_Machine.[Shaft], Machines.[Machines per Shaft], CTE_Machine.[Total Drilled]/(CTE_Machine.[Shift Count] * Machines.[Machines per Shaft]) As mMS
FROM CTE_Machine
	INNER JOIN 
		(
		SELECT [Shaft], count(DISTINCT concat([Date], [Shift])) As [Shift Count], COUNT(Distinct [Machine Number]) [Machines per Shaft]
		FROM [Performance]
		Where [Date] >= Date1 And [Date] <= Date2
		Group By [Shaft]
		) AS Machines ON CTE_Machine.[Shaft] = Machines.[Shaft]

Open in new window

Vitor Montalvão

This last one was more close from my last solution.
Glad that you could improve it.

ASKER

Yes your solution was the best way by far. Thanks again