NewbieMe NewbieMe - 5 months ago 7
SQL Question

Optimize/simplify this SQL query

I have this query and it works, but I know this can be simplified:

SELECT class, COUNT(class), publisher FROM books WHERE publisher = 'Company ABC' GROUP BY class
UNION
SELECT class, COUNT(class), publisher FROM books WHERE publisher = 'Company DEF' GROUP BY class
UNION
SELECT class, COUNT(class), publisher FROM books WHERE publisher = 'Company GHI' GROUP BY class
UNION
SELECT class, COUNT(class), publisher FROM books WHERE publisher = 'Company JKL' GROUP BY class
UNION
SELECT class, COUNT(class), publisher FROM books WHERE publisher = 'Company MNO' GROUP BY class;


It also hard-coded but I really want for the query to look for distinct companies in the table:

SELECT DISTINCT publisher FROM books;


Result:

publisher
Company ABC
Company DEF
Company GHI
Company JKL
Company MNO

Answer

Your query is malformed. Your DBMS should reject it with an error. You say it "works", so I guess it's doing what you want, but I'm not sure I can advise you on how to improve it.

The basic problem is

SELECT class,
       COUNT(class) as N,
       publisher
FROM books
GROUP BY class

With an aggregate (COUNT), the GROUP BY clause must include every non-aggregate in the SELECT clause. In your case, it must say

GROUP BY class, publisher

If you say only GROUP BY class, what does the count represent? My hope is that the DBMS is being a little be "smart": in your query, publisher is a single value, so COUNT is the number of non-NULL class values per class for that publisher. That is the equivalent of

SELECT class,
       COUNT(class) as N,
       publisher
FROM books
WHERE publisher in (
      'Company ABC', 'Company DEF', 'Company GHI',
  'Company JKL', 'Company MNO'
  )
GROUP BY class, publisher
;

If that does what you want, it's definitely an improvement. It's standard SQL, and will almost certainly execute faster.

Comments