Count the item when appearing more than once in the column

1

I have a query showing me all my stock. But I need to count just the items which appearing more than once in the column. I know I have to use group by and having count >1 statement but I don't know how to fit that into my query.
This is my query:

select
t1.lotnopre, t1.lotno, t2.locbin , t2.locstor , ((t2.locstock+t2.locqrstock)+(t2.locpicked+t2.locconsign))+t2.localloc , t1.lotqty , t1.account15_lot, t2.partno_alt

from livedb.mbc020 t2, livedb.mbc300 t1

where t1.account15_lot = '1 1 5' and t2."account15_alt"=t1."account15_lot" and t2."partno_alt"=t1."partno_lot" and t2."warehouse_alt"=t1."warehouse_lot" and t2."lotnopre_alt"=t1."lotnopre" and t2."lotno_alt"=t1."lotno"
and t1."lotnopre" <> ' '

group by t1.lotnopre, t1.lotno , t2.locbin , t2.locstor , ((t2.locstock+t2.locqrstock)+(t2.locpicked+t2.locconsign))+t2.localloc , t1.lotqty , t1.account15_lot, t2.partno_alt
having count(t1.lotno)>1
order by t1.lotno

The statement don't seems to work. It doesn't show me any information at all. I have tried in many ways fitting this into my query but I am new building the reports and don't really know what I am doing wrong.
I would appreciate any help.

2
  1. To get sensible answers you need to take the time to post consumable test data.
  1. Stop using the old JOIN systax.
SELECT ...
FROM livedb.mbc020 M020
	JOIN livedb.mbc300 M300
		ON M020.account15_alt = M300.account15_lot
			AND M020.partno_alt = M300.partno_lot
			AND M020.warehouse_alt = M300.warehouse_lot
			AND M020.lotnopre_alt = M300.lotnopre
			AND M020.lotno_alt = M300.lotno
WHERE M300.account15_lot = '1 1 5'
	AND M300."lotnopre" <> ' '
  1. So many columns in a GROUP BY looks suspect. You are asking for a COUNT of lotno within the group so it is not surprising no rows are returned. It is difficult to tell what you are trying to count without test data.
2 Likes
3

hi

hope this helps

create tables sample data script

CREATE TABLE IF NOT EXISTS livedb.mbc300 (
lotnopre VARCHAR(50),
lotno VARCHAR(100),
lotqty INT,
account15_lot VARCHAR(50),
partno_lot VARCHAR(100),
warehouse_lot VARCHAR(100)
);

CREATE TABLE IF NOT EXISTS livedb.mbc020 (
account15_alt VARCHAR(50),
partno_alt VARCHAR(100),
warehouse_alt VARCHAR(100),
lotnopre_alt VARCHAR(50),
lotno_alt VARCHAR(100),
locbin VARCHAR(50),
locstor VARCHAR(50),
locstock INT,
locqrstock INT,
locpicked INT,
locconsign INT,
localloc INT
);

-- Insert sample data into mbc300
INSERT INTO livedb.mbc300 (lotnopre, lotno, lotqty, account15_lot, partno_lot, warehouse_lot) VALUES
('PRE1', 'L001', 100, '1 1 5', 'PROD1', 'WARE1'),
('PRE1', 'L001', 50, '1 1 5', 'PROD1', 'WARE2'), -- Duplicate lotno L001
('PRE2', 'L002', 200, '1 1 5', 'PROD2', 'WARE1'),
('PRE2', 'L002', 150, '1 1 5', 'PROD2', 'WARE2'), -- Duplicate lotno L002
('PRE3', 'L003', 300, '1 1 5', 'PROD3', 'WARE1'),
('PRE3', 'L004', 250, '1 1 5', 'PROD3', 'WARE2'), -- Unique lotno L004
('PRE4', 'L001', 75, '1 1 5', 'PROD4', 'WARE3'); -- Duplicate lotno L001

-- Insert sample data into mbc020
INSERT INTO livedb.mbc020 (account15_alt, partno_alt, warehouse_alt, lotnopre_alt, lotno_alt, locbin, locstor, locstock, locqrstock, locpicked, locconsign, localloc) VALUES
('1 1 5', 'PROD1', 'WARE1', 'PRE1', 'L001', 'BIN1', 'STOR1', 50, 20, 30, 0, 10),
('1 1 5', 'PROD1', 'WARE2', 'PRE1', 'L001', 'BIN2', 'STOR2', 25, 15, 20, 0, 5),
('1 1 5', 'PROD2', 'WARE1', 'PRE2', 'L002', 'BIN3', 'STOR3', 100, 50, 40, 0, 20),
('1 1 5', 'PROD2', 'WARE2', 'PRE2', 'L002', 'BIN4', 'STOR4', 75, 30, 35, 0, 15),
('1 1 5', 'PROD3', 'WARE1', 'PRE3', 'L003', 'BIN5', 'STOR5', 150, 60, 50, 0, 25),
('1 1 5', 'PROD3', 'WARE2', 'PRE3', 'L004', 'BIN6', 'STOR6', 200, 80, 60, 0, 30),
('1 1 5', 'PROD4', 'WARE3', 'PRE4', 'L001', 'BIN7', 'STOR7', 50, 25, 20, 0, 10);

SELECT 
    t1.lotnopre,
    t1.lotno,
    t2.locbin,
    t2.locstor,
    ((t2.locstock + t2.locqrstock) + (t2.locpicked + t2.locconsign)) + t2.localloc,
    t1.lotqty,
    t1.account15_lot,
    t2.partno_alt
FROM 
    livedb.mbc020 t2,
    livedb.mbc300 t1
WHERE 
    t1.account15_lot = '1 1 5'
    AND t2."account15_alt" = t1."account15_lot"
    AND t2."partno_alt" = t1."partno_lot"
    AND t2."warehouse_alt" = t1."warehouse_lot"
    AND t2."lotnopre_alt" = t1."lotnopre"
    AND t2."lotno_alt" = t1."lotno"
    AND t1."lotnopre" <> ' '
GROUP BY 
    t1.lotno
HAVING 
    COUNT(t1.lotno) > 1
ORDER BY 
    t1.lotno;
4

I sent a note to the mods.

"This user repeatedly posts low effort replies from LLMs, many of which are just going to waste people's time. I've wanted to report them for the longest time, but I just never felt like being a jerk. But now I'm willing to say this user's replies are detrimental to the users of this site, and I ask that you consider banning them."

Hope this helps.

5

are you talking about me SqlHippo ?

did you ever check my profile ?

Please check and THINK before you say anything ?
See my profile and talk

did you even consider having a chat with me so that we can discuss ?

Banning is not going to bother me at all ,, at all means at all

See your profile vs my profile ??
:rofl: :rofl: :rofl:

6

there are SEVERAL things i can talk about you and in general ALSO

"not worth" my time and Thoughts and energy

7

OK, then let's discuss it.

Why do you think this particular solution will solve their problem?

8

did you generate the TEST DATA ? and give ?

users not having etiquette? i did that

why do you "not think" ? this particular solution will solve their problem
instead of asking me ? punching holes ? all that non sense ?

you provide a working solution ... explain it and thats it

9

Because it doesn't work. How did you test it?

10

Here is a working solution, adapted from yours.
It makes several assumptions we have to make unless the OP clarifies the requirements further.

11

you are right

i did not test it

Right frame of mind .. Thanks

:stuck_out_tongue_winking_eye:

12

i mean

you are in the "Right frame of mind"

:stuck_out_tongue_winking_eye: :wink: :wink: