-1

I have a table #1 as shown in image attached. First i want to sum all quantity of all distinct id. Then want to show number of id that have same quantity.

enter image description here

Felix Pamittan
  • 31,544
  • 7
  • 41
  • 67
Jani Mani
  • 27
  • 6
  • Can you explain to me the relation of total Id and the second table? I'm having trouble understanding – Toza May 24 '16 at 08:32
  • @NemanjaT there is no relation. Actually there is just 1 table exists. – Jani Mani May 24 '16 at 08:38
  • Look at Felix's answer, the query I came up with is nearly identical as his. – Toza May 24 '16 at 08:39
  • @NemanjaT there is problem in his query. It's only returning direct from table 1. While i want to add qty first for id let us say 1 from table 1. then again want to check how many id's have qty sum as id 1 – Jani Mani May 24 '16 at 08:41

3 Answers3

6

Use SUM and COUNT:

SELECT
    COUNT(*) AS totalId,
    qty
FROM (
    SELECT
        id, SUM(qty) AS qty
    FROM tbl
    GROUP BY id
)t
GROUP BY qty

ONLINE DEMO

Community
  • 1
  • 1
Felix Pamittan
  • 31,544
  • 7
  • 41
  • 67
  • It's returning just one record that is '1289002, 1' – Jani Mani May 24 '16 at 08:33
  • the query is good though, so it has to be something else that's troubling you. – Toza May 24 '16 at 08:38
  • there is problem in his query. It's only returning direct from table 1. While i want to add qty first for id let us say 1 from table 1. then again want to check how many id's have qty sum as id 1 – Jani Mani May 24 '16 at 08:42
  • His query is good, just make a temp table and insert results of first query into it, after that do outer select .... or just make 2 queries, first with inner select and 2nd one as both inner and outer select – Veljko89 May 24 '16 at 08:46
  • @JaniMani: This query perfectly solves the problem you stated. – dnoeth May 24 '16 at 08:48
  • @Veljko89 can you make it for me please – Jani Mani May 24 '16 at 08:48
  • @dnoeth, no it's not what i am looking for – Jani Mani May 24 '16 at 08:56
  • @JaniMani Write it for you? This website is for solving already existing code... not a code give-away website... – Toza May 24 '16 at 09:04
  • How you gonna learn if someone else would write it for you? google SQL temp table, write it down and learn something new ... you have all you need right in front of you, give it a shot, if you fail, come back, show us what you have done and we will help you after you have tried it yourself first – Veljko89 May 24 '16 at 09:12
  • 2
    @Jani Mani: We all agree here that this answers your question perfectly. As you can see from the linked demo, the query returns exactly the result you are looking for. So you should accept this answer. If you have an addtional request, create a new separate question. – Thorsten Kettner May 24 '16 at 09:13
  • Thanks everyone for covering for me. @JaniMani, does this solve your problem? – Felix Pamittan May 24 '16 at 10:06
  • @FelixPamittan Yes Man... Thanks U Specially :) – Jani Mani May 24 '16 at 10:15
1

Try this one after creating a temporary table

create table #Temp
(
  id int, 
  qty int
)

Insert Into #Temp
SELECT  id, SUM(qty)
        FROM yourTable
        group by id

SELECT  * FROM #Temp

SELECT  Count(id) , qty
        FROM #Temp
        GROUP BY qty
        ORDER BY qty DESC
reza.cse08
  • 5,938
  • 48
  • 39
0

to show the sum of all quantities of all distinct id:

SELECT id,SUM(qty) FROM table GROUP BY id;

to show number of id that have same quantity

SELECT count(id),quantity FROM (SELECT id,SUM(qty) AS quantity FROM table GROUP BY id) GROUP BY quantity
Mohamed Nabli
  • 1,629
  • 3
  • 17
  • 24
  • Well, this is the same answer @FelixPamittan wrote 15 minutes earlier :) – dnoeth May 24 '16 at 08:47
  • @dnoeth, It's not like that. I want to SUM in TABLE 1 and want to get result like Table 2. Then from Table 2 i want to get result like Required Table – Jani Mani May 24 '16 at 08:55