CODEWITHSUNDEEP
sqlms-access-2010
sam
sam

Reputation: 945

Access sql groupby

I Have a Table1 

 pbsc   qty   wt

 pbsc1   1    0  
 pbsc2   2    10
 pbsc3   1    0
 pbsc2   2    9    
 pbsc1   0    8
 pbsc4   9    9

I'm trying to get 2 results sets(2 query tables) from Table1

Sum up qty and wt if pbsc has similar data,for that query i used 

SELECT Table1.pbsc, sum(Table1.qty) As quantity , sum(Table1.wt) As
> Weight
>       FROM Table1 group by Table1.pbsc;

which gives me

pbsc    quantity    Weight
pbsc1   1             8
pbsc2   4            19
pbsc3   1             0
pbsc4   9             9

But i'm trying to get only pbsc1,pbsc2 as one result set since they occur more than once in a Table1, as result1 

  pbsc    qty    wt
  pbsc1     1     8
  pbsc2     4     19

result2 should be like (since pbsc3,pbsc4 occur only once)

  pbsc   qty   wt
  pbsc3   1    0
  pbsc4   9    9

Upvotes: 0

Views: 29

Answers (2)

Drone
Drone

Reputation: 1

SELECT Table1.pbsc,
       SUM(Table1.qty) AS Quantity,
       SUM(Table1.wt) AS  Weight
FROM Table1
WHERE Table1.pbsc IN (psc1, pbsc2)
GROUP BY Table1.pbsc

SELECT Table1.pbsc,
       SUM(Table1.qty) AS quantity,
       SUM(Table1.wt) AS  Weight
FROM Table1
WHERE Table1.pbsc IN (psc3, pbsc4)
GROUP BY Table1.pbsc

Upvotes: 0

Teja
Teja

Reputation: 13524

--More than one pbsc.

SELECT Table1.pbsc,
       sum(Table1.qty) As quantity ,
       sum(Table1.wt) As  Weight
  FROM Table1
 group by Table1.pbsc
 having count(*) > 1;

--Only one record for pbsc.

SELECT Table1.pbsc,
       sum(Table1.qty) As quantity ,
       sum(Table1.wt) As  Weight
  FROM Table1
 group by Table1.pbsc
 having count(*) = 1;

Upvotes: 1

Related Questions