can you help me with this query?? 2

[bprego]

the union duplicates the Level2ID and Level2Name and I can't use the query this way. can any one help me out, PLEASE? the code is posted below.

SELECT distinct Level2ID, Level2Name, count(Level3Name)as TOTAL_SUBS, '' as TOTAL_COMPANIES
FROM tbl_Prod_Categories
WHERE level2name in (Select distinct Level2name from tbl_Prod_Categories)
AND Level4ID IS NULL
and Level2ID is NOT NULL
And Level1ID = MMColParam2
group by Level2ID, Level2Name
union
SELECT distinct Level2ID, Level2Name, '' as TOTAL_SUBS, count(company_id) as TOTAL_COMPANIES
FROM tbl_Prod_Categories right outer join listing
on categoryID = NewcatID
WHERE level2name in (Select distinct Level2name from tbl_Prod_Categories)
AND Level4ID IS NULL
AND Level3ID IS NULL --Notice this is not in the upper portion of the UNION and it cant be
and Level2ID is NOT NULL
And Level1ID = MMColParam2
AND processedYN = 'Y'
group by Level2ID, Level2Name
ORDER BY Level2Name ASC


my output looks like this right now
Level2ID Level2Name TOTAL_SUBS TOTAL_COMPANIES
52 Audio-Visual 5 0
22 Broadband 0 0 --must remove!!
22 Broadband 0 14
26 Communication 5 0
27 Transmission 5 0

but what I need is this
Level2ID Level2Name TOTAL_SUBS TOTAL_COMPANIES
52 Audio-Visual 5 0
22 Broadband 0 14
26 Communication 5 0
27 Transmission 5 0
Thanks in advance.

 

[hneal98]

Here is something quick and dirty:

select Level2ID, Level2Name, sum( TOTAL_SUBS), sum(TOTAL_COMPANIES)
from
(SELECT distinct Level2ID, Level2Name, count(Level3Name)as TOTAL_SUBS, '' as TOTAL_COMPANIES
FROM tbl_Prod_Categories
WHERE level2name in (Select distinct Level2name from tbl_Prod_Categories) AND Level4ID IS NULL and Level2ID is NOT NULL And Level1ID = MMColParam2
group by Level2ID, Level2Name

union

SELECT distinct Level2ID, Level2Name, '' as TOTAL_SUBS, count(company_id) as TOTAL_COMPANIES
FROM tbl_Prod_Categories right outer join listingon categoryID = NewcatID
WHERE level2name in (Select distinct Level2name from tbl_Prod_Categories)
AND Level4ID IS NULL AND Level3ID IS NULL --Notice this is not in the upper portion of the UNION and it cant be
and Level2ID is NOT NULL And Level1ID = MMColParam2 AND processedYN = 'Y'
group by Level2ID, Level2Name)
group by Level2ID, Level2Name
ORDER BY Level2Name ASC


I just made your query a subquery in the from clause and then sum both of those fields.

Hope it works for you.

 

[bprego]

thanks for your help but I am getting a syntax error
by the second group by that i cant figure out. any idea why? I will play with your idea for a while longer to see if i can make it work for me.

 

[hneal98]

What was the error?

 

[bprego]

Incorrect syntax near the keyword 'group'.

 

[hneal98]

Try debugging your inner queries first. Try taking each query and running it by it self and see if one of them gives you the error. The only thing i did was put that into a () set and put it in a from clause. That should not have caused any problems. Let me know how it comes out.

 

[sunila7]

A few changes in hneal's query.

select Level2ID, Level2Name, sum( TOTAL_SUBS), sum(TOTAL_COMPANIES)
from
(SELECT distinct Level2ID, Level2Name, count(Level3Name)as TOTAL_SUBS, '' as TOTAL_COMPANIES
FROM tbl_Prod_Categories
WHERE level2name in (Select distinct Level2name from tbl_Prod_Categories) AND Level4ID IS NULL and Level2ID is NOT NULL And Level1ID = MMColParam2
group by Level2ID, Level2Name

union

SELECT distinct Level2ID, Level2Name, '' as TOTAL_SUBS, count(company_id) as TOTAL_COMPANIES
FROM tbl_Prod_Categories right outer join listing on categoryID = NewcatID
WHERE level2name in (Select distinct Level2name from tbl_Prod_Categories)
AND Level4ID IS NULL AND Level3ID IS NULL --Notice this is not in the upper portion of the UNION and it cant be
and Level2ID is NOT NULL And Level1ID = MMColParam2 AND processedYN = 'Y'
group by Level2ID, Level2Name) TBL
group by Level2ID, Level2Name
ORDER BY Level2Name ASC

Sunil

 

[bprego]

It works great.
sunila7 thank you so much for your help and hneal98 you too. You just saved me from having to work through the weekend on this project. THANKS

 

[mwolf00]

bprego -

"sunila7 thank you so much for your help and hneal98 you too. You just saved me from having to work through the weekend on this project. THANKS"

sounds like a pretty helpful post. If you think so, you can click "Mark this post as a helpful/expert post!" to mark it as such...

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[hneal98]

Right, Sorry, I forgot about qualifying it. I had that same problem about a month ago.

Thanks sunila.