Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 247
  • Last Modified:

Is there someway I can return this in a single query as a single dataset?

Select count(ColumnA),ColumnB,ColumnC
From Table A
Where dateA > '01/01/2012'

AND

Select count(ColumnA),ColumnB,ColumnC    
From TableA
0
rhservan
Asked:
rhservan
  • 5
  • 4
  • 4
1 Solution
 
Ron MalmsteadInformation Services ManagerCommented:
Select count(ColumnA),ColumnB,ColumnC
From TableA A
Where dateA > '01/01/2012'

UNION ALL

Select count(ColumnA),ColumnB,ColumnC    
From TableA  B
0
 
rhservanAuthor Commented:
The count column in each query needs to remain as separate count columns.  Of course the difference being is one has the date filter the other does not.
0
 
hnasrCommented:
Modifying your query:

Select count(ColumnA),ColumnB,ColumnC
From Table A
Where dateA > '01/01/2012'
GROUP BY ColumnB, ColumnC
Union
Select count(ColumnA),ColumnB,ColumnC    
From TableA
GROUP BY ColumnB, ColumnC
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
hnasrCommented:
"The count column in each query needs to remain as separate count columns. "
What do you mean? Give example.
0
 
rhservanAuthor Commented:
What I really need is this:

ColumnA1  ColumnA2   ColumnB    ColumnC

I need to maintain the two different count columns.  If I use UNION or UNION ALL - won't the counts just be merged?
0
 
Ron MalmsteadInformation Services ManagerCommented:
Try this..
Let me know if this is what you're after.

Select count(ColumnA) As Table1Count,'' as Table2Count,ColumnB,ColumnC
From TableA A
Where dateA > '01/01/2012'

UNION ALL

Select '' as Table1Count, count(ColumnA) as Table2Count,ColumnB,ColumnC    
From TableA  B
0
 
Ron MalmsteadInformation Services ManagerCommented:
Or...

Select count(ColumnA),TotalCount=(SELECT Count(ColumnA) FROM TableA),ColumnB,ColumnC
From TableA A
Where dateA > '01/01/2012'
0
 
rhservanAuthor Commented:
ColumnA1&A2 are the same column with just the date filter difference.
I need ColumnA1 count based on the date filter
I need ColumnA2 count based on without date filter

ColumnA1   ColumnA2    
   500              10000
0
 
Ron MalmsteadInformation Services ManagerCommented:
right,...try my last suggestion.
That should work.

Select count(ColumnA) as ColumnA1,ColumnA2=(SELECT Count(ColumnA) FROM TableA),ColumnB,ColumnC
From TableA A
Where dateA > '01/01/2012'
0
 
hnasrCommented:
This will be a repeating column values, right?
List few records and the required output.

Select (Select count(ColumnA),ColumnB,ColumnC
From Table A Where dateA > '01/01/2012') As ColumnA ,
                   (Select count(ColumnA),ColumnB,ColumnC
From Table A) As ColumnA2,
                   ColumnB,ColumnC
From Table A
0
 
rhservanAuthor Commented:
You are correct in that it gives the Total count for ColumnA2, but It doesn't breakout by name as in ColumnA1, see below:

ColumnB      ColumnA1        ColumnA2
Name1                 6                       540
Name2                 16                     540
Name3                 3                       540
Name4                 14                     540
Name5                  1                      540

So ColumnA2 doesn't appear to be using the main query portion for other things.
0
 
hnasrCommented:
What is the raw data

ColumnA ColumnB
----               Name1
----                ----
To produce the above shown result?
0
 
rhservanAuthor Commented:
Raw Data
ColumnB - Full Name
ColumnA1 - interaction dates  
ColumnA2 - interaction dates
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 5
  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now