CODEWITHSUNDEEP
sql-servergroup-bysql-server-2012
Tony Brix
Tony Brix

Reputation: 4241

SQL count number of groups

I have a table with pallets, items, item quantity:

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 sub-query?

Upvotes: 10

Views: 11657

Answers (2)

juergen d
juergen d

Reputation: 204756

Yes, use DISTINCT

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

Upvotes: 14

Chetan Bodke
Chetan Bodke

Reputation: 523

Simply use Distinct to avoid duplicate records to be count.

count(Distinct pallet)

Your query like this 

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

it will give output AS :

it will give output AS :

Upvotes: 2

Related Questions