rhservan
asked on
Is there someway I can return this in a single query as a single dataset?
Select count(ColumnA),ColumnB,Col umnC
From Table A
Where dateA > '01/01/2012'
AND
Select count(ColumnA),ColumnB,Col umnC
From TableA
From Table A
Where dateA > '01/01/2012'
AND
Select count(ColumnA),ColumnB,Col
From TableA
ASKER
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.
Modifying your query:
Select count(ColumnA),ColumnB,Col umnC
From Table A
Where dateA > '01/01/2012'
GROUP BY ColumnB, ColumnC
Union
Select count(ColumnA),ColumnB,Col umnC
From TableA
GROUP BY ColumnB, ColumnC
Select count(ColumnA),ColumnB,Col
From Table A
Where dateA > '01/01/2012'
GROUP BY ColumnB, ColumnC
Union
Select count(ColumnA),ColumnB,Col
From TableA
GROUP BY ColumnB, ColumnC
"The count column in each query needs to remain as separate count columns. "
What do you mean? Give example.
What do you mean? Give example.
ASKER
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?
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?
Try this..
Let me know if this is what you're after.
Select count(ColumnA) As Table1Count,'' as Table2Count,ColumnB,Column C
From TableA A
Where dateA > '01/01/2012'
UNION ALL
Select '' as Table1Count, count(ColumnA) as Table2Count,ColumnB,Column C
From TableA B
Let me know if this is what you're after.
Select count(ColumnA) As Table1Count,'' as Table2Count,ColumnB,Column
From TableA A
Where dateA > '01/01/2012'
UNION ALL
Select '' as Table1Count, count(ColumnA) as Table2Count,ColumnB,Column
From TableA B
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
I need ColumnA1 count based on the date filter
I need ColumnA2 count based on without date filter
ColumnA1 ColumnA2
500 10000
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'
That should work.
Select count(ColumnA) as ColumnA1,ColumnA2=(SELECT Count(ColumnA) FROM TableA),ColumnB,ColumnC
From TableA A
Where dateA > '01/01/2012'
This will be a repeating column values, right?
List few records and the required output.
Select (Select count(ColumnA),ColumnB,Col umnC
From Table A Where dateA > '01/01/2012') As ColumnA ,
(Select count(ColumnA),ColumnB,Col umnC
From Table A) As ColumnA2,
ColumnB,ColumnC
From Table A
List few records and the required output.
Select (Select count(ColumnA),ColumnB,Col
From Table A Where dateA > '01/01/2012') As ColumnA ,
(Select count(ColumnA),ColumnB,Col
From Table A) As ColumnA2,
ColumnB,ColumnC
From Table A
ASKER
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.
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.
What is the raw data
ColumnA ColumnB
---- Name1
---- ----
To produce the above shown result?
ColumnA ColumnB
---- Name1
---- ----
To produce the above shown result?
ASKER
Raw Data
ColumnB - Full Name
ColumnA1 - interaction dates
ColumnA2 - interaction dates
ColumnB - Full Name
ColumnA1 - interaction dates
ColumnA2 - interaction dates
From TableA A
Where dateA > '01/01/2012'
UNION ALL
Select count(ColumnA),ColumnB,Col
From TableA B