CODEWITHSUNDEEP
mysqlsql-server
Tobias Timpe
Tobias Timpe

Reputation: 770

Converting MySQL to SQL Server

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?

Upvotes: 2

Views: 275

Answers (7)

Taryn
Taryn

Reputation: 247630

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

Upvotes: 2

Bacon Bits
Bacon Bits

Reputation: 32145

@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.

Upvotes: 0

Tobias Timpe
Tobias Timpe

Reputation: 770

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 :)

Upvotes: 0

Alex
Alex

Reputation: 23300

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

Upvotes: 0

Olaf Dietsche
Olaf Dietsche

Reputation: 74018

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.

Upvotes: 0

Kaf
Kaf

Reputation: 33809

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

Upvotes: 1

bummi
bummi

Reputation: 27367

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 ;

Upvotes: 1

Related Questions