Solved

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

Posted on 2013-05-13
13
241 Views
Last Modified: 2013-05-13
Select count(ColumnA),ColumnB,ColumnC
From Table A
Where dateA > '01/01/2012'

AND

Select count(ColumnA),ColumnB,ColumnC    
From TableA
0
Comment
Question by:rhservan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 4
13 Comments
 
LVL 25

Expert Comment

by:Ron Malmstead
ID: 39162128
Select count(ColumnA),ColumnB,ColumnC
From TableA A
Where dateA > '01/01/2012'

UNION ALL

Select count(ColumnA),ColumnB,ColumnC    
From TableA  B
0
 

Author Comment

by:rhservan
ID: 39162177
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
 
LVL 30

Expert Comment

by:hnasr
ID: 39162252
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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
LVL 30

Expert Comment

by:hnasr
ID: 39162256
"The count column in each query needs to remain as separate count columns. "
What do you mean? Give example.
0
 

Author Comment

by:rhservan
ID: 39162280
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
 
LVL 25

Expert Comment

by:Ron Malmstead
ID: 39162297
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
 
LVL 25

Accepted Solution

by:
Ron Malmstead earned 500 total points
ID: 39162309
Or...

Select count(ColumnA),TotalCount=(SELECT Count(ColumnA) FROM TableA),ColumnB,ColumnC
From TableA A
Where dateA > '01/01/2012'
0
 

Author Comment

by:rhservan
ID: 39162327
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
 
LVL 25

Expert Comment

by:Ron Malmstead
ID: 39162337
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
 
LVL 30

Expert Comment

by:hnasr
ID: 39162522
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
 

Author Comment

by:rhservan
ID: 39162533
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
 
LVL 30

Expert Comment

by:hnasr
ID: 39162553
What is the raw data

ColumnA ColumnB
----               Name1
----                ----
To produce the above shown result?
0
 

Author Comment

by:rhservan
ID: 39162600
Raw Data
ColumnB - Full Name
ColumnA1 - interaction dates  
ColumnA2 - interaction dates
0

Featured Post

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Database Restore 2008 R2 1 27
SQL Server Express or Standard? 5 34
Database-Scoped Permissions 2 18
Can a Trigger trigger a Trigger? 4 24
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question