I need help with a new querry again

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

I need help with a new querry again

Author

Topic

Alina
Starting Member

20 Posts

Posted - 2007-11-12 : 02:30:53

Hello. I know, you think I'm totally clueless, and you're right. SQL is the most confusing thing for me but I must learn it.
So, I am running a query to find out the name of the person who sold the most stoves with the least color purchased. I have ran that query and it works

. My problem is that I have to add to that query another column that counts the number of stoves sold, instead of having them displayed one by one. My query is as follows:

SELECT EMPLOYEE.Name,STOVE.Color FROM INV_LINE_ITEM, STOVE, EMPLOYEE, INVOICE
WHERE INV_LINE_ITEM.FK_Stovenbr=STOVE.SerialNumber
AND INV_LINE_ITEM.FK_InvoiceNbr=INVOICE.InvoiceNbr
AND INVOICE.FK_EmpID=EMPLOYEE.EmpID
AND STOVE.Color IN (SELECT CAST(MIN(Color) AS CHAR(5)) 'Min Color' FROM STOVE)
AND EMPLOYEE.Name IN (Select CAST(MAX(Name) AS CHAR(15)) 'Employee Name' FROM EMPLOYEE)
ORDER BY EMPLOYEE.Name, STOVE.Color

Thank you

Alina
Starting Member

20 Posts

Posted - 2007-11-12 : 03:04:32

Hello again. Please ignore my question. I figured it out.
Thanks
Alina

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-12 : 03:53:07

quote:
Originally posted by Alina

Hello again. Please ignore my question. I figured it out.
Thanks
Alina

Post your solution

Madhivanan

Failing to plan is Planning to fail

Alina
Starting Member

20 Posts

Posted - 2007-11-12 : 03:57:14

this was my solution :)

SELECT EMPLOYEE.Name,STOVE.Color, COUNT(*) AS 'Stove Count' FROM INV_LINE_ITEM, STOVE, EMPLOYEE, INVOICE
WHERE INV_LINE_ITEM.FK_Stovenbr=STOVE.SerialNumber
AND INV_LINE_ITEM.FK_InvoiceNbr=INVOICE.InvoiceNbr
AND INVOICE.FK_EmpID=EMPLOYEE.EmpID
AND STOVE.Color IN (SELECT CAST(MIN(Color) AS CHAR(5)) 'Min Color' FROM STOVE)
AND EMPLOYEE.Name IN (Select CAST(MAX(Name) AS CHAR(15)) 'Employee Name' FROM EMPLOYEE)
GROUP BY EMPLOYEE.Name, STOVE.Color

Alina
It was simple afterall, or so it seems.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources