a_sandoval avatar image
a_sandoval asked

I need to get the results of my original query, and just grouping again

Select DivisionNumber, count (distinct DGCNBR) From (select DivisionNumber, count (distinct DGCNBR) From dbo.CBDGREP with (NOLOCK) inner join dbo.CBBLREP with (NOLOCK) ON BLTITL = DGTITL and BLSHNB = DGSHNB inner join dbo.House with (NOLOCK) ON DGHNUM = HouseNumber inner join dbo.CBDKREP with (NOLOCK) ON DKNROV = DGNROV and DKCNBR = DGCNBR and DKHNUM = DGHNUM and DKTITL = DGTITL and DKSHNB = DGSHNB Where DGACL8 between '2013-03-01' and '2013-03-31' and DKVAF4 > 0 and DKSTYX IN ('B') and BLVNCD NOT IN ('ESPN1','ESPN2','ESPN3','ESPN4','ESPN5','ESPN6','EXTY', 'TEN','TENXTY','EXTASY','VHC','PLYBOY','PLYBY2','HUSTLR','PENT','REAL','JUICY','BRAZZR', 'BANGU','IN1HD') and DKVAF4 NOT IN ('19.99','24.99','29.99','34.99','39.99','44.99','49.99', '54.99','16.81','12.99','9.99','14.99','11.99','7.99','22.49','27.49','25.21') Group By DivisionNumber Union Select DivisionNumber, count (distinct LICNBR) "Count" From dbo.ITLICPP with (NOLOCK), dbo.ITLMREP with (NOLOCK), dbo.House with (NOLOCK), dbo.Customer with (NOLOCK) Where AccountNumber = LICNBR and LINROV = LMNROV and LICERR = LMCERR and LINUVP = LMNUVP and LIHNUM = HouseNumber and LIDDAA between '2013-03-01' and '2013-03-31' and LICERR NOT IN ('100','101','102','151','190','191','193','195','196','197','201','232') and CustomerTypeCode NOT IN ('8','U') and LIVALU NOT IN ('0.00','19.99','24.99','29.99','34.99','39.99','44.99','49.99', '54.99','16.81','12.99','9.99','14.99','11.99','7.99','4.99','22.49','27.49', '5.99','6.49','6.99','17.49','25.21') Group By DivisionNumber) Group By DivisionNumber
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

can you add to the question? Is the query you listed not giving the desired results?
0 Likes 0 ·
Actually I was curious about an alias going with the query and figured out that I needed to put an Alias on each column in my query because I was getting an error and after the subquery (the parentheses). I also cleaned up the format a little so it was easier to read or examine. I was looking for one set of results (the subquery) with the 2 queries I had with the union.
0 Likes 0 ·
I did get it figured out, thank you. I was making it harder then it really should have been. I am still working on the format a litle (my joins in the second query) but should be good to go. I understand subqueries and the alias a little more after working with my boss on it as well. Thanks again for your reply!
0 Likes 0 ·
do you have what you need then? It sounds like you've solved it if you've also aliased the entire subquery - select divisionnumber, count(distinct DGCNBR) from (subquery) as sourcequery group by divisionnumber.
0 Likes 0 ·

0 Answers


Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.