How can I create query that (counting values inside Column)

Question

I have 3 companies 1001,1002 ,1003 it could be more and 11 containers with different sizes 1,2,3,4, 5 I want return only the containers that are in the companies that have the same amount or more of specified numbers. for example if I want 2 containers from size 1 and 3 containers from size 2 then only the containers in the company that has 2 or more of size 1 and 3 or more of size 2 should appear let's say that only company 1001 has them then it should appear alone. I tried different queries and post one here but they recommend me to post a new question with the problem that I'm training to make query for.

(Company info and containers info are in two separate tables)

this is what I get when I remove having (basically all the containers in the city that has been selected)

CoID    CoName          ContainerID   Price size1 size2  size3 size4 size5
6000001 hbjjvCompany    2000002       50    1     0      0     0     0
6000001 hbjjvCompany    2000003       50    1     0      0     0     0
6000002 NCompany        2000004       50    1     0      0     0     0
6000001 hbjjvCompany    2000005       100   0     1      0     0     0
6000002 NCompany        2000007       100   0     1      0     0     0
6000001 hbjjvCompany    2000008       200   0     0      1     0     0
6000001 hbjjvCompany    2000009       200   0     0      1     0     0
6000001 hbjjvCompany    2000010       200   0     0      1     0     0
6000002 NCompany        2000011       200   0     0      1     0     0
6000001 hbjjvCompany    2000012       400   0     0      0     0     1
6000003 ghhaCo          2000014       200   0     1      0     0     0

what should I get is

CoID    CoName                  size1 size2  size3 size4 size5
6000001 hbjjvCompany            2     1      3     0     1

of course I want the containers id and the price but I put it heare like this to make it clear that my query show all the containers even if i removed the ContainerID and price.

Hi, as you are quite new here, please allow me some hints in order to help you to get answers "to the point". If you refer to another question (I suppose it's [this](http://stackoverflow.com/q/35878249/5089204), you should include a link. And please: Create a test scenario (either post SQL here or create a [Fiddle](www.sqlfiddle.com). Add test data and the expected output. Thx — Shnugo, Mar 09 '16 at 09:03

score 0 · Accepted Answer · answered Mar 09 '16 at 10:36

0

I think it's this you're looking for:

CREATE TABLE #YourTable(CoID INT,CoName VARCHAR(100),ContainerID INT,Price DECIMAL(10,4),size1 INT,size2 INT,size3 INT,size4 INT,size5 INT);
INSERT INTO #YourTable VALUES
 (6000001,'hbjjvCompany',2000002,50,1,0,0,0,0)
,(6000001,'hbjjvCompany',2000003,50,1,0,0,0,0)
,(6000002,'NCompany',2000004,50,1,0,0,0,0)
,(6000001,'hbjjvCompany',2000005,100,0,1,0,0,0)
,(6000002,'NCompany',2000007,100,0,1,0,0,0)
,(6000001,'hbjjvCompany',2000008,200,0,0,1,0,0)
,(6000001,'hbjjvCompany',2000009,200,0,0,1,0,0)
,(6000001,'hbjjvCompany',2000010,200,0,0,1,0,0)
,(6000002,'NCompany',2000011,200,0,0,1,0,0)
,(6000001,'hbjjvCompany',2000012,400,0,0,0,0,1)
,(6000003,'ghhaCo',2000014,200,0,1,0,0,0);

SELECT CoID
      ,CoName
      ,SUM(Price) AS SumPrice
      ,SUM(size1) AS CountSize1
      ,SUM(size2) AS CountSize2
      ,SUM(size3) AS CountSize3
      ,SUM(size4) AS CountSize4
      ,SUM(size5) AS CountSize5
FROM #YourTable
GROUP BY CoID,CoName;

--Clean up
DROP TABLE #YourTable;

The result

CoID    CoName           SumPrice  s1  s2  s3  s4  s5
6000003 ghhaCo           200.0000   0   1   0   0   0
6000001 hbjjvCompany    1200.0000   2   1   3   0   1
6000002 NCompany         350.0000   1   1   1   0   0

answered Mar 09 '16 at 10:36

Shnugo

66,100
9
53
114

No I'm not trying to get the sum of each column. – Nysa Mar 09 '16 at 10:41
@Nysa Well, the result you posted is exactly this. Your numbers seem to be counts of containers of a given size. The `SUM` of counts is the accumulated count... What else do you need? – Shnugo Mar 09 '16 at 10:43
What I want is to count each size in the original containerSize column in the container table then compare them with given number I posted it in my previous question http://stackoverflow.com/questions/35878249/msg-156-level-15-state-1-line-22-incorrect-syntax-near-the-keyword-having/35878376?noredirect=1#comment59423022_35878376 – Nysa Mar 09 '16 at 11:14
@Nysa, In your question you state an expected output for CoID=6000001. This is absolutely the same as my second line... I'm puzzled... – Shnugo Mar 09 '16 at 11:19
Sorry I couldn't see the complete result in my phone I checked it now in my pc. It's correct but I want return only the company that possess the same amount or more that I specify for each size. – Nysa Mar 09 '16 at 11:33
@Nysa It's the easiest way to pack the whole query as a CTE and use this as if it was a table. You can use `WHERE` or any other extension With this you avoid your troubles with column aliases... Do you know what I'm speaking about? – Shnugo Mar 09 '16 at 11:36
I will tell you what I understood and please tell me if it's wrong. what I have to do is to separate them for simplicity then use where for my conditions but why (insert ) can't this be achieved without creating temporary table ? – Nysa Mar 09 '16 at 12:01
@Nysa the CTE is kind of temporary table. But it is inlined (*ad-hoc sql*). Not absolutely the same but close to... – Shnugo Mar 09 '16 at 12:06
hmm. Isn't it time consuming? and I want to ask about the insert. my data is from different tables the data that is written in the insert you wrote is from different tables in my database . it's the same right? Sorry if I'm asking a lot. I don't have that much of experience in the database. – Nysa Mar 09 '16 at 12:21

How can I create query that (counting values inside Column)

1 Answers1