SQL Query help - 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

SQL Server 2000 Forums

SQL Server Development (2000)

SQL Query help

Author

Topic

dupati1
Posting Yak Master

123 Posts

Posted - 2004-03-30 : 12:34:37

Hi Guys,
I have this below query which gives me the list and total of all active inventory grouped by different inventory categories.

******************
rsStat.Open "SELECT m.InventID, COUNT(*) AS totalinvent,t.Name as InventName FROM InventMain m join InventType t on m.InventID=t.InventID WHERE m.ActiveInd=1 GROUP BY m.InventID, t.Name ORDER BY t.Name ASC", MdConnection
******************
Now my question is -- how can i also get the total of all inactive inventory. Do i need to write a complete new query for it or can i make changes to the above query to get this result.

Presently i am displaying in the table format as shown below and works fine

InventoryID| Inventory Name| Total Active
___________________________________________

but now i want something like this

InventoryID| Inventory Name| Total Active | Total Inactive
__________________________________________________________

Any Suggestions.

Thanks

VJ

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-03-30 : 12:42:16

Assuming that inactive is where m.ActiveInd = 0 this should work:

rsStat.Open "SELECT m.InventID, SUM(m.ActiveInd) AS totalinvent, SUM(CASE WHEN m.ActiveInd = 0 THEN 1 ELSE 0 END) as TotalInactiveInvent, t.Name as InventName FROM InventMain m join InventType t on m.InventID=t.InventID GROUP BY m.InventID, t.Name ORDER BY t.Name ASC", MdConnection

Duane.

dupati1
Posting Yak Master

123 Posts

Posted - 2004-03-30 : 12:50:40

Hi Duane,

Thanks for your reply. With minor changes i got the query working as i desired.

Thanks a lot.

VJ

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources