March 1, 2007 at 10:08 am
I already have a solution to my problem, but I don't like it very much, and I'm wondering if there's another way I can make this more efficient.
I have two tables. Here's some really, really simplified versions of these tables (the tables actually have many other columns as well):
PreviousCarrierForWC
Id CHAR(20) - my primary key
CarrierName VARCHAR(60)
PreviousCarrierForBA
Id CHAR(20) - my primary key
txtPriorCarrier VARCHAR(60)
Essentially CarrierName and txtPriorCarrier store the same type of data. What I want is a list of all the distinct CarrierNames and a count of how many times they are in the table.
For example, I have these data sets...
PreviousCarrierForWC
| Id | CarrierName |
| 12345678901234567890 | AO |
| 23456789012345678901 | AO |
| 34567890123456789012 | AAA |
| 45678901234567890123 | Something else |
PreviousCarrierForBA
| Id | CarrierName |
| 12345678901234567890 | Test |
| 23456789012345678901 | Sample data |
| 34567890123456789012 | AAA |
| 45678901234567890123 | AO |
And I want the following dataset returned:
Expected Result Set:
| CarrierName | Total |
| AO | 3 |
| AAA | 2 |
| Something else | 1 |
| Sample data | 1 |
| Test | 1 |
I achieved this using a temp table:
DECLARE @TempTable TABLE
(CarrierName VARCHAR( 60 ))
INSERT INTO @TempTable
SELECT CarrierName
FROM PreviousCarrierForWC
UNION ALL
SELECT txtPriorCarrier
FROM PreviousCarrierForBA
SELECT CarrierName, COUNT(*) AS Total
FROM @TempTable
GROUP BY CarrierName
ORDER BY CarrierName, Total DESC
DELETE @TempTable
But what I really wanted was just one SELECT similar to the following...
select distinct carriername, count(*) as Total
from PreviousCarrierForWC
group by CarrierName
union
select distinct txtpriorcarrier, count(*) as Total
from PreviousCarrierForBA
group by txtpriorcarrier
order by carriername, Total DESC
The problem is that this query returns a resultset like the following...
| CarrierName | Total |
| AO | 2 |
| AO | 1 |
| AAA | 1 |
| AAA | 1 |
| Something else | 1 |
| Sample data | 1 |
| Test | 1 |
AO and AAA are repeated, and I actually want these totals to be added together. If anyone has any suggestions, I'd be happy to hear them.
March 1, 2007 at 10:15 am
Do the UNION ALL in a derived table. Something like:
SELECT CarrierName
,COUNT(*) AS Total
FROM (
SELECT CarrierName
FROM PreviousCarrierForWC
UNION ALL
SELECT txtPriorCarrier
FROM PreviousCarrierForBA
) D
GROUP BY CarrierName
March 1, 2007 at 10:19 am
Oh! I was trying to do something like this before, but I hadn't given the derived table an alias and I was getting errors. I just thought this wasn't possible. Thanks for that!
March 6, 2007 at 4:28 am
Hi ,
Try This....Its working
select txtPriorCarrier,count(*) a from(
select * from PreviousCarrierForBA
union all
select * from PreviousCarrierForwc)a
group by txtPriorCarrier
order by 2 desc
Regards,
Amit G.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply