query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

query

Author

Topic

Alina
Starting Member

20 Posts

Posted - 2007-11-12 : 07:31:38

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

30421 Posts

Posted - 2007-11-12 : 08:13:23

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"

Alina
Starting Member

20 Posts

Posted - 2007-11-12 : 08:18:04

i didn't know that you were not suppose to. thanks peso. it does make sense

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-12 : 08:21:32

[code]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[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources