Converting MySQL to SQL Server

Question

I'm trying to run the following query (against Foodmart 2000), which runs fine on MySQL. On SQL Server 2005, all I get is a "Syntax error near '" message.

SELECT * FROM ((SELECT COUNT(state_province) AS 'WA'
                FROM customer c1
                WHERE c1.state_province LIKE 'WA'
                GROUP BY c1.state_province) as t1,
               (SELECT COUNT(state_province) AS 'OR'
                FROM customer c1
                WHERE c1.state_province LIKE 'OR'
                GROUP BY c1.state_province) as t2,
               (SELECT COUNT(state_province) AS 'CA'
                FROM customer c1
                WHERE c1.state_province LIKE 'CA'
                GROUP BY c1.state_province) as t3);

What exactly causes this problem?

I think you should give an alias name for main table. SELECT * FROM (.....) tbl — arunes
– arunes, Commented Dec 4, 2012 at 12:20
What's the input and what's the expected output of that query ? — Alex
– Alex, Commented Dec 4, 2012 at 12:34

Taryn · Accepted Answer · 2012-12-04 13:06:32Z

2

You are missing an alias:

SELECT * 
FROM 
(
  select *
  from 
  (SELECT COUNT(state_province) AS 'WA'
   FROM customer c1
   WHERE c1.state_province LIKE 'WA'
   GROUP BY c1.state_province) as t1,
  (SELECT COUNT(state_province) AS 'OR'
   FROM customer c1
   WHERE c1.state_province LIKE 'OR'
   GROUP BY c1.state_province) as t2,
   (SELECT COUNT(state_province) AS 'CA'
    FROM customer c1
    WHERE c1.state_province LIKE 'CA'
    GROUP BY c1.state_province) as t3
) src;  -- < add this alias

You must have an alias on all derived tables and subqueries.

You can also use a version of @Kaf's still with count by using the following:

SELECT count(CASE WHEN state_province LIKE 'WA' THEN 1 ELSE null END) AS 'WA',
       count(CASE WHEN state_province LIKE 'OR' THEN 1 ELSE null END) AS 'OR',
       count(CASE WHEN state_province LIKE 'CA' THEN 1 ELSE null END) AS 'CA'
FROM customer

see SQL Fiddle with Demo

edited Dec 4, 2012 at 13:06

answered Dec 4, 2012 at 12:21

Taryn

249k57 gold badges374 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tobias Timpe Over a year ago

I just tried running exactly that, and I still get the same error.

Olaf Dietsche Over a year ago

Adding comments doesn't help, see SQL Fiddle.

Indi · Accepted Answer · 2012-12-04 12:36:22Z

1

Try this; single SELECT using SUM instead of COUNT

SELECT SUM(CASE WHEN state_province LIKE 'WA' THEN 1 ELSE 0 END) AS 'WA',
       SUM(CASE WHEN state_province LIKE 'OR' THEN 1 ELSE 0 END) AS 'OR',
       SUM(CASE WHEN state_province LIKE 'CA' THEN 1 ELSE 0 END) AS 'CA'
FROM customer --c1
--GROUP BY c1.state_province

Edit: Working demo, SQL Fiddle here

edited Dec 4, 2012 at 12:36

answered Dec 4, 2012 at 12:29

Indi

33.9k7 gold badges61 silver badges80 bronze badges

Comments

bummi · Accepted Answer · 2012-12-04 12:26:56Z

1

SELECT * FROM (Select (SELECT COUNT(state_province) AS 'WA'
                FROM customer c1
                WHERE c1.state_province LIKE 'WA'
                GROUP BY c1.state_province) as t1,
               (SELECT COUNT(state_province) AS 'OR'
                FROM customer c1
                WHERE c1.state_province LIKE 'OR'
                GROUP BY c1.state_province) as t2,
               (SELECT COUNT(state_province) AS 'CA'
                FROM customer c1
                WHERE c1.state_province LIKE 'CA'
                GROUP BY c1.state_province) as t3) a ;

answered Dec 4, 2012 at 12:26

bummi

27.4k14 gold badges67 silver badges105 bronze badges

Comments

Olaf Dietsche · Accepted Answer · 2012-12-04 12:41:39Z

0

Just leave out the braces around your from subselects and remove the group by, they are unnecessary

SELECT * FROM (SELECT COUNT(state_province) AS 'WA'
               FROM customer c1
               WHERE c1.state_province LIKE 'WA') as t1,
              (SELECT COUNT(state_province) AS 'OR'
               FROM customer c1
               WHERE c1.state_province LIKE 'OR') as t2,
              (SELECT COUNT(state_province) AS 'CA'
               FROM customer c1
               WHERE c1.state_province LIKE 'CA') as t3;

See SQL Fiddle for testing.

answered Dec 4, 2012 at 12:41

Olaf Dietsche

74.4k9 gold badges113 silver badges214 bronze badges

Comments

Alex · Accepted Answer · 2012-12-04 12:46:05Z

0

I'd rather PIVOT my data

SELECT [WA] AS [WA], [OR] AS [OR], [CA] AS [CA] FROM
(SELECT state_province FROM customer) C
PIVOT
(
  COUNT(C.state_province) FOR state_province IN ([WA],[OR],[CA])
) AS pivoted

Fiddle here

answered Dec 4, 2012 at 12:46

Alex

23.3k4 gold badges43 silver badges76 bronze badges

Comments

Tobias Timpe · Accepted Answer · 2012-12-04 12:46:12Z

0

Well, first of all, thanks for all the suggestions. All of you were right, I was missing an alias. Also, as it turns out, I had to add a "use foodmart2000" before the statement. Stupid me :)

answered Dec 4, 2012 at 12:46

Tobias Timpe

7702 gold badges15 silver badges29 bronze badges

Comments

Bacon Bits · Accepted Answer · 2012-12-04 12:46:31Z

0

@bluefeet and @bummi are extremely close. The issue is that you can't use the as keyword with table aliases in SQL Server:

SELECT * 
FROM 
(
  (SELECT COUNT(state_province) AS 'WA'
   FROM customer c1
   WHERE c1.state_province LIKE 'WA'
   GROUP BY c1.state_province) t1,
  (SELECT COUNT(state_province) AS 'OR'
   FROM customer c1
   WHERE c1.state_province LIKE 'OR'
   GROUP BY c1.state_province) t2,
   (SELECT COUNT(state_province) AS 'CA'
    FROM customer c1
    WHERE c1.state_province LIKE 'CA'
    GROUP BY c1.state_province) t3
) src;

You will probably get better performance from @kaf's example, however.

answered Dec 4, 2012 at 12:46

Bacon Bits

32.5k6 gold badges63 silver badges79 bronze badges

Collectives™ on Stack Overflow

Converting MySQL to SQL Server

7 Answers 7

2 Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

2 Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related