February 17, 2010 at 10:56 am
Hi all,
I have to merge data for a table is nothing is available actually,
I have a query that picks data from one table for every date, now for example the data for today came like this:
--DATE AREA CITY COUNT
--Feb 17 2010 NORTHNY2
--Feb 17 2010 SOUTHNY1
--Feb 17 2010 EASTNY2
--Feb 17 2010 NORTHPA1
--Feb 17 2010 EASTPA3
--Feb 17 2010 SOUTHFL1
Now there is possibilty that the data may not be there for some area of for some city so in that case i want to show count as 0 from my query but still want to show all possible combinations for AREA and CITY
i.e.
NORTH NY
SOUTH NY
EAST NY
and
NORTH PA
SOUTH PA
EAST PA
and so on...but count as 0 for all...
here is the code:
CREATE TABLE [dbo].[#test](
[DATE] [varchar](50) NULL,
[varchar](50) NULL,
[CITY] [varchar](50) NULL,
[MY_COUNT] [int] NULL
)
INSERT INTO #test ( DATE,AREA,CITY,MY_COUNT) VALUES ( 'Feb 17 2010 12:00AM', 'NORTH', 'NY', 2)
INSERT INTO #test ( DATE,AREA,CITY,MY_COUNT) VALUES ( 'Feb 17 2010 12:00AM', 'SOUTH', 'NY', 1)
INSERT INTO #test ( DATE,AREA,CITY,MY_COUNT) VALUES ( 'Feb 17 2010 12:00AM', 'EAST', 'NY', 2)
INSERT INTO #test ( DATE,AREA,CITY,MY_COUNT) VALUES ( 'Feb 17 2010 12:00AM', 'NORTH', 'PA', 1)
INSERT INTO #test ( DATE,AREA,CITY,MY_COUNT) VALUES ( 'Feb 17 2010 12:00AM', 'EAST', 'PA', 3)
INSERT INTO #test ( DATE,AREA,CITY,MY_COUNT) VALUES ( 'Feb 17 2010 12:00AM', 'SOUTH', 'FL', 1)
select * from #test
Is there any way I can do this , i think i hav to merge it but am not sure how to???
Thanks in advance...
Thanks [/font]
February 17, 2010 at 3:26 pm
No offense, are you asking how to do a group by?
If so, then I've added another row in the table because all your sample data are unique so group by doesn't show anything different
INSERT INTO #test ( DATE,AREA,CITY,MY_COUNT) VALUES ( 'Feb 17 2010 12:00AM', 'NORTH', 'NY', 3)
then you can do:
select [DATE],AREA,CITY,sum(MY_COUNT) from #test
group by [DATE],AREA,CITY
you will see for NY North the sum is 5 (3+2).
Your other thing about returning 0 but perserving other columns is only possible if you hard code all the combination for Area, City into another table, lets call it AreaCity and then do
select B.[Date], A.Area, A.city, isnull(Sum(B.MY_COUNT),0)
from Areacity as A left join #test B
on A.Area=B.Area
and A.City=B.city
group by B.[DATE],A.AREA,A.CITY
In my Areacode table I've added "South", "TX" so the result is
Date Areacity(No column name)
NULL SouthTX0
Feb 17 2010 12:00AMEastNY2
Feb 17 2010 12:00AMEastPA3
Feb 17 2010 12:00AMNorthNY5
Feb 17 2010 12:00AMNorthPA1
Feb 17 2010 12:00AMSouthFL1
Feb 17 2010 12:00AMSouthNY1
If I misunderstood you, please explain.
February 18, 2010 at 10:49 am
Hi Thanks For Your Reply Actually Thats What I Want , I Wanna Store Count 0 Values In One Test Table And Use Data From That Table If I Dont Get Any Value From The Main Table I Created A Sample Table With All Possible Combinations Of Area And City And Used A Union All QUERY to get the data from both the tables, but I am not getting and data as expected, instead I am getting 2 -2 rows for the vallues that are present in both the tables
for ex:like this:
EASTPA3
EASTPA0
EASTNY0
EASTNY2
Here is the code for union all, I want only one row for EAST PA and EAST NY in this case
I dont want the 0 rows to be shown as I am getting counts for them from the main table....
hope you got it...
here is my code for both tables:
-----------------------ORIGINAL TABLE--------------------------------
CREATE TABLE [dbo].[#test](
[varchar](50) NULL,
[CITY] [varchar](50) NULL,
[MY_COUNT] [int] NULL
)
INSERT INTO #test ( AREA,CITY,MY_COUNT) VALUES ( 'NORTH', 'NY', 2)
INSERT INTO #test ( AREA,CITY,MY_COUNT) VALUES ( 'SOUTH', 'NY', 1)
INSERT INTO #test ( AREA,CITY,MY_COUNT) VALUES ( 'EAST', 'NY', 2)
INSERT INTO #test ( AREA,CITY,MY_COUNT) VALUES ( 'NORTH', 'PA', 1)
INSERT INTO #test ( AREA,CITY,MY_COUNT) VALUES ( 'EAST', 'PA', 3)
INSERT INTO #test ( AREA,CITY,MY_COUNT) VALUES ( 'SOUTH', 'FL', 1)
----------------sample TABLE FOR DATA COUNT =0 --------------------
CREATE TABLE [dbo].[#MYtest3](
[varchar](50) NULL,
[CITY] [varchar](50) NULL,
[MY_COUNT] [int] NULL
)
INSERT INTO #MYtest3 ( AREA,CITY,MY_COUNT) VALUES ( 'NORTH', 'NY', 0)
INSERT INTO #MYtest3 ( AREA,CITY,MY_COUNT) VALUES ( 'SOUTH', 'NY', 0)
INSERT INTO #MYtest3 ( AREA,CITY,MY_COUNT) VALUES ( 'EAST', 'NY', 0)
INSERT INTO #MYtest3 ( AREA,CITY,MY_COUNT) VALUES ( 'NORTH', 'PA', 0)
INSERT INTO #MYtest3 ( AREA,CITY,MY_COUNT) VALUES ( 'EAST', 'PA', 0)
INSERT INTO #MYtest3 ( AREA,CITY,MY_COUNT) VALUES ( 'SOUTH', 'FL', 0)
INSERT INTO #MYtest3 ( AREA,CITY,MY_COUNT) VALUES ( 'SOUTH', 'PA', 0)
INSERT INTO #MYtest3 ( AREA,CITY,MY_COUNT) VALUES ( 'NORTH', 'FL', 0)
INSERT INTO #MYtest3 ( AREA,CITY,MY_COUNT) VALUES ( 'EAST', 'FL', 0)
----------------- union all query --------------------
select * from #test
UNION all
select * from #MYtest3
ORDER BY 1,2 DESC
thanks for ur help
Thanks [/font]
February 18, 2010 at 1:17 pm
What about the Date that was your original question? I guess you can figure that out on your own.
For these two tables, there are at least these two ways to get what you want:
1. select Area, city, sum(my_count)
from
(
select * from #test
UNION all
select * from #MYtest3
) a
group by area, city
ORDER BY 1,2 DESC
This is your query wrapped inside a Group By so I can add up the counts, that way there won't be a 0 if there are other values for the same Area, City combination.
2. select A.Area, A.city, isnull(B.MY_COUNT,0)
from #mytest3 as A left join #test B
on A.Area=B.Area
and A.City=B.city
order by 1, 2 desc
This is similar to what I wrote before, using an IsNull to select the non zero value if there is a row in #test, otherwise use 0 (which is essentially the value in #mytest3).
Please let me know if you have any questions.
February 18, 2010 at 2:21 pm
Hey thanks man...ur code works like gem...
thanks again.........:-)
Thanks [/font]
February 18, 2010 at 2:55 pm
You are welcome. This wasn't your homework, is it? 😀
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply