Group by? Order by?

Databases
1

I need to create a report on the number of exams a hospital has done.

That table looks kinda like this.

Acc#, Study ID
A_1, 123
B_2, 333
A_1, 234
A_2, 666
B_1, 456

I am asked to give a report of the number of studies done for A_% and B_%
A_1 is a bit of a special case as it should either count as 1 study or two depending on billing practices.

I got this to work where A_1 was assumed to be ‘unique’ where in actuality it isn’t always.
Study ID ‘is’ unique!

2

What database engine?

3

Hi Kem!
sqlite.

I get the feeling that the SQL language has flavours… some features like temporary tables etc may not work.

4

Without taking into consideration the special “A_1” conditions, something like this might be what you’re looking for:

SELECT
  SUBSTR(acc, 1, 1) AS acc,
  COUNT(*) AS count
FROM
  my_table
GROUP BY
  1;

(Not tested.)

For more complex groupings, you can use CASE WHEN x THEN y END for the first element of the SELECT.

5

Kem, what if I were to concatenate the study id and acc together?

then select %_% ?

6

Your question makes me think I don’t understand what you’re trying to do.

7

A_1_123
A_1_234
A_2_666

So how many are ‘like’ A_1?

8 
SELECT
  acc,
  COUNT(*)
FROM
  my_table
GROUP BY
  1

No concat needed.

9

Thanks Ken!
The count(*)… Why not something other than *?

10

It doesn’t really matter.

11

I miss COBOL.