Number of SQL Counts

Question

I have a table with pallets, elements, number of elements:

pallet | item | qty
-------------------
  1        1     2
  1        2     4
  2        3     2
  2        5     3
  3        4     4

I need to find count (pallet), count (item), sum (qty)

count(pallets) | count(items) | sum(qty)
----------------------------------------
      3                5           15

I can get the sum (qty) and count (item) with

select count(0) as totalItems, sum(qty) as total from table

Is there a way to get the number of pallets without a subquery?

Answer 1

Yes use DISTINCT

select count(distinct pallet) as pallets,
       sum(qty) as total, 
       count(0) as totalItems 
from your_table

Answer 2

Just use Distinct to avoid duplicate entries.

count(Distinct pallet)

Your request is like this

select 
      count(distinct pallet) as pallets,
      sum(qty) as Total, 
      count(item) AS [Total Items]

it will produce the result of AS: