Help to get efficient Query

Question

question

J Harris asked Feb 18, '10

Help to get efficient Query

It's been very long since I've looked at SQL, so I apologize in advance...

I have a simplified table with two columns shown below with its column headings:

ID      bitValue
101     1
102     0
103     1

I want to write a query that will return two rows, the first a count of all rows in the table and the second a count of all rows that have bitValue set to 1. So for the above table the result of the query will be:

3
2

Is obvious how to write a separate query for each row, but I was hoping there is a trick using a single sub query or something for efficiency.

query

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-02-18T15:41:43Z

Matt Whitfield answered Feb 18, '10

You want

SELECT COUNT(*) FROM myTable
UNION ALL
SELECT COUNT(*) FROM myTable WHERE bitValue = 1

More efficient would be two columns

SELECT COUNT(*) as AllCount, SUM(CASE WHEN bitValue = 1 THEN 1 ELSE 0 END) As SetCount
FROM myTable

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2010-02-18T15:41:29Z

Grant Fritchey answered Feb 18, '10

You could use a UNION ALL query in this case. Each of the two queries that will be required will join together without having to try to join data that is not really in a state to be joined.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 3 · 2010-02-18T23:11:29Z

J Harris answered Feb 18, '10

Thanks for the quick answers. Looks like the two separate selects with union all is the way for two rows, which I was trying to avoid. I was able to rework the solution to be a single row and used the COUNT & SUM suggestion.

Thanks again...

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 4 · 2010-02-20T04:14:09Z

Jeff Moden answered Feb 20, '10

I want to write a query that will return two rows

I'm not complaining... I'm just curious why you asked for two rows when one row would have been easier to begin with and appears to be what you settled for.

--Jeff Moden

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question