Use COUNT in a table based on two values returned

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)

Use COUNT in a table based on two values returned

Author

Topic

Vanny
Starting Member

9 Posts

Posted - 2008-12-19 : 08:59:11

The below query works great and pulls back a lot of information I need, there is just one last piece of data I want to pull back and so far I have been unsuccessful in figuring out how to do it with just one result set.

For each record returned, I need a count from the TotalsName table where UpperID is equal to the ID in that record and UpperUser is equal to the UpperUser in that record.
So for each record, I want to use two of the values returned to give me a count of records in another table based on that criteria, in one result set. Is this possible? Any help is appreciated.

----SQL----
SELECT ADR, T.UpperUser, T.ID, T.Amount
FROM User A
LEFT OUTER JOIN Name N ON A.UserNumber = N.UpperUser
LEFT OUTER JOIN Totals T ON A.UserNumber = T.UpperUser
WHERE A.DoneDate IS NULL AND N.Type IN (0,1) AND N.ADR='00001' AND T.Amount > 0

UNION

SELECT TN.ADR, TN.UpperUser, T.ID, T.Amount
FROM User A
LEFT OUTER JOIN Totals T ON A.UserNumber = T.UpperUser
LEFT OUTER JOIN TotalsName TN ON T.UpperUser = TN.UpperUser AND T.ID = TN.UpperID
WHERE A.DoneDate IS NULL AND TN.Type = 1 AND ADR='00001' AND T.Amount > 0

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-12-19 : 09:24:27

Just check the alias names in your query. I don't see any alias called "T",but you are using that in your query here.
As for the count, you could use something like this,

SELECT ADR, T.UpperUser, T.ID, T.Amount,cnt.tot
FROM User A
LEFT OUTER JOIN Name N ON A.UserNumber = N.UpperUser
LEFT OUTER JOIN Totals S ON A.UserNumber = T.UpperUser
LEFT JOIN (SELECT UpperUser,[ID],count(*) as tot FROM TotalsName GROUP BY UpperUser,[ID])cnt ON cnt.UpperUser=UpperUser and cnt.[ID]=[ID]
WHERE A.DoneDate IS NULL AND N.Type IN (0,1) AND N.ADR='00001' AND T.Amount > 0 

UNION


SELECT TN.ADR, TN.UpperUser, T.ID, T.Amount,cnt.tot
FROM User A
LEFT OUTER JOIN Totals S ON A.UserNumber = T.UpperUser
LEFT OUTER JOIN TotalsName SN ON T.UpperUser = TN.UpperUser AND T.ID = TN.UpperID
LEFT JOIN (SELECT UpperUser,[ID],count(*) as tot FROM TotalsName GROUP BY UpperUser,[ID])cnt ON cnt.UpperUser=UpperUser and cnt.[ID]=[ID]
WHERE A.DoneDate IS NULL AND TN.Type = 1 AND ADR='00001' AND T.Amount > 0

Vanny
Starting Member

9 Posts

Posted - 2008-12-19 : 10:58:05

Yea I botched some of the aliases when I was renaming, but your query works great! Thanks you. Only thing I noticed is I seem to be getting duplicates, so I will get one record with the count as null, and then one with a count 1 or 2. I think the group by is forcing this to happen. I'm trying to just get a total count. I added AND IS NOT NULL, to the end to get around this, any ideas? Am I doing it correctly?

Edit: I fixed the aliases in my above query.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources