SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Alina
Starting Member

Romania
20 Posts

Posted - 11/12/2007 :  07:31:38  Show Profile  Reply with Quote
Hello again. Here's my problem. I have the following querry:

SELECT INVOICE.FK_EmpID, EMPLOYEE.Name, COUNT(DISTINCT(STOVE.Type + ' ' + Stove.Version)) 'Stove List'
FROM STOVE, INVOICE, EMPLOYEE, INV_LINE_ITEM
WHERE EMPLOYEE.EmpID=INVOICE.FK_EmpID
AND INVOICE.InvoiceNbr=INV_LINE_ITEM.FK_InvoiceNbr
AND INV_LINE_ITEM.FK_StoveNbr=STOVE.SerialNumber
GROUP BY INVOICE.FK_EmpID, Name, STOVE.Type + ' ' + Stove.Version

The query displays what I want but, on top of that I need to count the stove lists per each employee, thus showing how many different stove versions each employee sold and select the one with the lowest amount of stove types sold. I know I would need top 1 and sort asc, but I also need to somehow count the stuff from COUNT(DISTINCT(STOVE.Type + ' ' + Stove.Version)) 'Stove List' or employee.name .. that's the confusing part to me. how do i count that on top of all that I have running so far, plus after I have to choose the lowest #.

Thank you for the help. I'd appreciate any suggestion.
Alina

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 11/12/2007 :  08:13:23  Show Profile  Visit SwePeso's Homepage  Reply with Quote
You are grouping by the column you are counting with?
SELECT		i.FK_EmpID,
		e.Name,
		COUNT(DISTINCT s.Type + ' ' + s.Version) AS [Stove List]
FROM		STOVE AS s
INNER JOIN	INV_LINE_ITEM AS ili ON ili.FK_StoveNbr = s.SerialNumber
INNER JOIN	INVOICE AS i ON i.InvoiceNbr = ili.FK_InvoiceNbr
INNER JOIN	EMPLOYEE AS e ON e.EmpID = i.FK_EmpID
GROUP BY	i.FK_EmpID,
		e.Name



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

Alina
Starting Member

Romania
20 Posts

Posted - 11/12/2007 :  08:18:04  Show Profile  Reply with Quote
i didn't know that you were not suppose to. thanks peso. it does make sense
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 11/12/2007 :  08:21:32  Show Profile  Visit SwePeso's Homepage  Reply with Quote
SELECT		i.FK_EmpID,
		e.Name,
		s.Type
		COUNT(DISTINCT s.Version) AS [Stove List]
FROM		STOVE AS s
INNER JOIN	INV_LINE_ITEM AS ili ON ili.FK_StoveNbr = s.SerialNumber
INNER JOIN	INVOICE AS i ON i.InvoiceNbr = ili.FK_InvoiceNbr
INNER JOIN	EMPLOYEE AS e ON e.EmpID = i.FK_EmpID
GROUP BY	i.FK_EmpID,
		e.Name,
		s.Type

SELECT		i.FK_EmpID,
		e.Name,
		s.Type,
		s.Version
		COUNT(*) AS [Stove List]
FROM		STOVE AS s
INNER JOIN	INV_LINE_ITEM AS ili ON ili.FK_StoveNbr = s.SerialNumber
INNER JOIN	INVOICE AS i ON i.InvoiceNbr = ili.FK_InvoiceNbr
INNER JOIN	EMPLOYEE AS e ON e.EmpID = i.FK_EmpID
GROUP BY	i.FK_EmpID,
		e.Name,
		s.Type,
		s.Version



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000