mysql query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

mysql query

Author

Topic

ab
Starting Member

1 Post

Posted - 2008-02-11 : 00:35:52

hi,
i have a table as follows:
location1 location2 Accident
a b 3
b a 5
c d 3
b c 4
c b 5
a b 2

I want to get the following output:

location1 location2 Accident
a b 10
c d 3
b c 9

I am not being able to write the correct sql query for this. Can somebody please help?
thanks,
A

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-02-11 : 00:54:18

quote:
mysql query

Is it MYSQL or Microsoft SQL Server ?

This is a MS SQL Server forum

KH
[spoiler]Time is always against us[/spoiler]

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2008-02-11 : 01:24:24

i don't understand the output needed. am I being dense?

-ec

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-02-11 : 01:32:18

[code]a b 10
from
a b 3
b a 5
a b 2

b c 9
from
b c 4
c b 5
[/code]

KH
[spoiler]Time is always against us[/spoiler]

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2008-02-11 : 01:36:46

yes, i was being dense

-ec

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-02-11 : 01:45:15

i understand what OP wants but have not figured out how to do it yet

KH
[spoiler]Time is always against us[/spoiler]

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2008-02-11 : 13:58:33

how about this?

CREATE TABLE [test] (col1 CHAR(1), col2 CHAR(1), col3 INT)

INSERT INTO [test] VALUES ('a', 'b', '3')
INSERT INTO [test] VALUES ('b', 'a', '5')
INSERT INTO [test] VALUES ('c', 'd', '3')
INSERT INTO [test] VALUES ('b', 'c', '4')
INSERT INTO [test] VALUES ('c', 'b', '5')
INSERT INTO [test] VALUES ('a', 'b', '2')

-- DROP TABLE [test]


SELECT 'Location' =
        CASE
                WHEN ASCII(col1) + ASCII(col2) = 195
                THEN 'ab'
                WHEN ASCII(col1) + ASCII(col2) = 197
                THEN 'bc'
                WHEN ASCII(col1) + ASCII(col2) = 199
                THEN 'cd'
                ELSE 'NA'
        END,
        SUM(col3) AS Total
FROM    test
GROUP BY ASCII(col1) + ASCII(col2)
ORDER BY Location ASC

don't know if this works in mySQL, but it works fine in MSSQL.

-ec

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-02-11 : 14:35:00

create table [#test] (col1 char(1), col2 char(1), col3 int)

insert into [#test] values ('a', 'b', '3')
insert into [#test] values ('b', 'a', '5')
insert into [#test] values ('c', 'd', '3')
insert into [#test] values ('b', 'c', '4')
insert into [#test] values ('c', 'b', '5')
insert into [#test] values ('a', 'b', '2')


select
	col1,
	col2,
	col3 =sum(col3)
from
	(
	select
		col1 = case when col1 < col2 then col1 else col2 end,
		col2 = case when col1 < col2 then col2 else col1 end,
		col3
	from
		#test
	) a
group by
	col1,
	col2
order by
	col1,
	col2

drop table #test

Results:

col1 col2 col3        
---- ---- ----------- 
a    b    10
b    c    9
c    d    3

(3 row(s) affected)

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-11 : 15:00:07

UNION ALL?

SELECT Location1, Location2, SUM(Accident) AS Accident
FROM (
SELECT Location1, Location2, Accident FROM Table1
UNION ALL
SELECT Location2, Location1, Accident FROM Table1
) AS x
GROUP BY Location1, Location2

E 12°55'05.25"
N 56°04'39.16"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-02-11 : 15:16:06

quote:
Originally posted by Peso

UNION ALL?

SELECT Location1, Location2, SUM(Accident) AS Accident
FROM (
SELECT Location1, Location2, Accident FROM Table1
UNION ALL
SELECT Location2, Location1, Accident FROM Table1
) AS x
GROUP BY Location1, Location2

E 12°55'05.25"
N 56°04'39.16"

That would work if you added this line:

where Location1 < Location2

Otherwise, you would get this:

Location1 Location2 Accident    
--------- --------- ----------- 
b         a         10
a         b         10
c         b         9
b         c         9
d         c         3
c         d         3

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-11 : 15:19:24

You're right!

Change SUM to SUM() / 2?

You're approach is more efficient. Only on SCAN/SEEK. My has two.

E 12°55'05.25"
N 56°04'39.16"

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2008-02-11 : 15:43:41

i like my solution because of it's ridiculousness and lack of scalability.

Even if this was originally a homework question by the OP, i think I am the one who learned something from MVJ and Peso. thx guys.

-ec

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-02-11 : 20:54:44

quote:
Originally posted by eyechart

i like my solution because of it's ridiculousness and lack of scalability.

Even if this was originally a homework question by the OP, i think I am the one who learned something from MVJ and Peso. thx guys.

-ec

At least you have a solution, I have to submit blank for the homework

But now i can copy from MVJ and Peso's solutions and got full marks for it

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources