All geek questions in one place

Count based on row and null

Here are my details:

id     FirstName      LastName     HouseNo     MyCount
1      A                  C          1-1         2
2      B                  C          1-1         2
4      D                  A                      3
5      F                  A                      3
6      J                  A                      3
7      Q                  X          1-2         3
8      D                  X          1-2         3
9      D                  X          1-2         3
10     A                  C          1-3         3
11     B                  C          1-3         3
12     C                  C          1-3         3
14     F                  K                      2
15     J                  K                      2
16     Q                  X          1-5         1

With the above data, I want to take the count of records with the same HouseNoand LastName.

For this I use

SELECT COUNT(ID) AS _COUNT FROM MYTABLE GROUP BY LASTNAME, HOUSENO

but the above statement has one problem. Some records do not have data HouseNo. In the example above, ID 4,5,6 and 14,15 do not have HouseNo. Thus, the above statement returns 5, but it should return 3 and 2 separately.

primary goal

  • Take an invoice based on LastNameandHouseNo
  • Take an account of those records that do not have HouseNo(they will arrive sequentially).
  • Incoming invoice must be updated in MyCount

How do I get this bill?

Edit for bounty:

Data examples

id  FirstName   LastName    HouseNo     MyCount     CountId
1   Imran       Khan        1-1         
2   Waseem      Khan        1-1         
3   Rihan       Khan        1-1         
4   Moiz        Shaikh      1-2         
5   Zbair       Shaikh      1-2         
6   Sultan      Shaikh      1-2         
7   Zaid        Khan                    
10  Parvez      Patel       1-3         
11  Ahmed       Patel       1-3         
12  Rahat       Syed        1-4         
13  Talha       Khan                    
14  Zia         Khan                    
15  Arshad      Patel       1-3         
16  Samad       Patel       1-3         
17  Raees       Syed        1-4         
18  Azmat       Khan                    
19  Imran       Khan

Expected Result:

id  FirstName   LastName    HouseNo     MyCount     CountId
1   Imran       Khan        1-1         3           1
2   Waseem      Khan        1-1         3           1
3   Rihan       Khan        1-1         3           1
4   Moiz        Shaikh      1-2         3           2
5   Zbair       Shaikh      1-2         3           2
6   Sultan      Shaikh      1-2         3           2
7   Zaid        Khan                    1           3
10  Parvez      Patel       1-3         2           4   
11  Ahmed       Patel       1-3         2           4
12  Rahat       Syed        1-4         1           5   
13  Talha       Khan                    2           6
14  Zia         Khan                    2           6   
15  Arshad      Patel       1-3         2           7   
16  Samad       Patel       1-3         2           7
17  Raees       Syed        1-4         1           8   
18  Azmat       Khan                    2           9
19  Imran       Khan                    2           9
  • In the samples, the data are MyCountboth CountIdempty and must be filled.
  • MyCount HouseNo LastName, . ID 1 3, - № 1-1, MyCount 1 3 3, CountId 1.
  • , HouseNo, . . ID 7, 1. . ID 18 19, 2.
  • CountId - . , . ID 1 3, 1 - .
+4
8

, SQL , GROUP BY LASTNAME, HOUSENO.

, . , , ( ), .

, gaps-and-islands. SQL Server 2008 , ROW_NUMBER.

DECLARE @T TABLE 
    (id int PRIMARY KEY
    ,FirstName nvarchar(50)
    ,LastName nvarchar(50)
    ,HouseNo nvarchar(50)
    ,MyCount int
    ,CountId int);

INSERT INTO @T (id, FirstName, LastName, HouseNo) VALUES
(1 , 'Imran ', 'Khan  ', '1-1'),
(2 , 'Waseem', 'Khan  ', '1-1'),
(3 , 'Rihan ', 'Khan  ', '1-1'),
(4 , 'Moiz  ', 'Shaikh', '1-2'),
(5 , 'Zbair ', 'Shaikh', '1-2'),
(6 , 'Sultan', 'Shaikh', '1-2'),
(7 , 'Zaid  ', 'Khan  ',  NULL),
(10, 'Parvez', 'Patel ', '1-3'),
(11, 'Ahmed ', 'Patel ', '1-3'),
(12, 'Rahat ', 'Syed  ', '1-4'),
(13, 'Talha ', 'Khan  ',  NULL),
(14, 'Zia   ', 'Khan  ',  NULL),
(15, 'Arshad', 'Patel ', '1-3'),
(16, 'Samad ', 'Patel ', '1-3'),
(17, 'Raees ', 'Syed  ', '1-4'),
(18, 'Azmat ', 'Khan  ',  NULL),
(19, 'Imran ', 'Khan  ',  NULL);

SELECT

WITH
CTE_RN
AS
(
    SELECT
        id
        ,FirstName
        ,LastName
        ,HouseNo
        ,MyCount
        ,CountId
        ,ROW_NUMBER() OVER (PARTITION BY LastName, HouseNo ORDER BY ID) AS rn1
        ,ROW_NUMBER() OVER (ORDER BY ID) AS rn2
    FROM @T AS T
)
,CTE_GRoups
AS
(
    SELECT
        id
        ,FirstName
        ,LastName
        ,HouseNo
        ,MyCount
        ,CountId
        ,rn1
        ,rn2
        ,rn2-rn1 AS GroupNumber
        ,COUNT(ID) OVER (PARTITION BY LastName, HouseNo, rn2-rn1) AS NewMyCount
        ,MIN(ID) OVER (PARTITION BY LastName, HouseNo, rn2-rn1) AS GroupMinID
    FROM CTE_RN
)
SELECT
    id
    ,FirstName
    ,LastName
    ,HouseNo
    ,rn1
    ,rn2
    ,GroupNumber
    ,NewMyCount
    ,GroupMinID
    ,DENSE_RANK() OVER (ORDER BY GroupMinID) AS NewCountId
FROM CTE_GRoups
ORDER BY ID;


+----+-----------+----------+---------+-----+-----+-------------+------------+------------+------------+
| id | FirstName | LastName | HouseNo | rn1 | rn2 | GroupNumber | NewMyCount | GroupMinID | NewCountId |
+----+-----------+----------+---------+-----+-----+-------------+------------+------------+------------+
|  1 | Imran     | Khan     | 1-1     |   1 |   1 |           0 |          3 |          1 |          1 |
|  2 | Waseem    | Khan     | 1-1     |   2 |   2 |           0 |          3 |          1 |          1 |
|  3 | Rihan     | Khan     | 1-1     |   3 |   3 |           0 |          3 |          1 |          1 |
|  4 | Moiz      | Shaikh   | 1-2     |   1 |   4 |           3 |          3 |          4 |          2 |
|  5 | Zbair     | Shaikh   | 1-2     |   2 |   5 |           3 |          3 |          4 |          2 |
|  6 | Sultan    | Shaikh   | 1-2     |   3 |   6 |           3 |          3 |          4 |          2 |
|  7 | Zaid      | Khan     | NULL    |   1 |   7 |           6 |          1 |          7 |          3 |
| 10 | Parvez    | Patel    | 1-3     |   1 |   8 |           7 |          2 |         10 |          4 |
| 11 | Ahmed     | Patel    | 1-3     |   2 |   9 |           7 |          2 |         10 |          4 |
| 12 | Rahat     | Syed     | 1-4     |   1 |  10 |           9 |          1 |         12 |          5 |
| 13 | Talha     | Khan     | NULL    |   2 |  11 |           9 |          2 |         13 |          6 |
| 14 | Zia       | Khan     | NULL    |   3 |  12 |           9 |          2 |         13 |          6 |
| 15 | Arshad    | Patel    | 1-3     |   3 |  13 |          10 |          2 |         15 |          7 |
| 16 | Samad     | Patel    | 1-3     |   4 |  14 |          10 |          2 |         15 |          7 |
| 17 | Raees     | Syed     | 1-4     |   2 |  15 |          13 |          1 |         17 |          8 |
| 18 | Azmat     | Khan     | NULL    |   4 |  16 |          12 |          2 |         18 |          9 |
| 19 | Imran     | Khan     | NULL    |   5 |  17 |          12 |          2 |         18 |          9 |
+----+-----------+----------+---------+-----+-----+-------------+------------+------------+------------+

, , . ROW_NUMBER s. rn1 LastName, HouseNo. LastName, HouseNo. rn2 - . , ID , .

GroupNumber.

COUNT, NewMyCount.

. MIN , DENSE_RANK NewCountId .

NewMyCount NewCountId, SELECT UPDATE:

UPDATE

WITH
CTE_RN
AS
(
    SELECT
        id
        ,FirstName
        ,LastName
        ,HouseNo
        ,MyCount
        ,CountId
        ,ROW_NUMBER() OVER (PARTITION BY LastName, HouseNo ORDER BY ID) AS rn1
        ,ROW_NUMBER() OVER (ORDER BY ID) AS rn2
    FROM @T AS T
)
,CTE_GRoups
AS
(
    SELECT
        id
        ,FirstName
        ,LastName
        ,HouseNo
        ,MyCount
        ,CountId
        ,rn1
        ,rn2
        ,rn2-rn1 AS GroupNumber
        ,COUNT(ID) OVER (PARTITION BY LastName, HouseNo, rn2-rn1) AS NewMyCount
        ,MIN(ID) OVER (PARTITION BY LastName, HouseNo, rn2-rn1) AS GroupMinID
    FROM CTE_RN
)
,CTE_Update
AS
(
    SELECT
        id
        ,FirstName
        ,LastName
        ,HouseNo
        ,MyCount
        ,CountId
        ,rn1
        ,rn2
        ,GroupNumber
        ,NewMyCount
        ,GroupMinID
        ,DENSE_RANK() OVER (ORDER BY GroupMinID) AS NewCountId
    FROM CTE_GRoups
)
UPDATE CTE_Update
SET
    MyCount = NewMyCount
    ,CountId = NewCountId
;


SELECT *
FROM @T
ORDER BY ID;

+----+-----------+----------+---------+---------+---------+
| id | FirstName | LastName | HouseNo | MyCount | CountId |
+----+-----------+----------+---------+---------+---------+
|  1 | Imran     | Khan     | 1-1     |       3 |       1 |
|  2 | Waseem    | Khan     | 1-1     |       3 |       1 |
|  3 | Rihan     | Khan     | 1-1     |       3 |       1 |
|  4 | Moiz      | Shaikh   | 1-2     |       3 |       2 |
|  5 | Zbair     | Shaikh   | 1-2     |       3 |       2 |
|  6 | Sultan    | Shaikh   | 1-2     |       3 |       2 |
|  7 | Zaid      | Khan     | NULL    |       1 |       3 |
| 10 | Parvez    | Patel    | 1-3     |       2 |       4 |
| 11 | Ahmed     | Patel    | 1-3     |       2 |       4 |
| 12 | Rahat     | Syed     | 1-4     |       1 |       5 |
| 13 | Talha     | Khan     | NULL    |       2 |       6 |
| 14 | Zia       | Khan     | NULL    |       2 |       6 |
| 15 | Arshad    | Patel    | 1-3     |       2 |       7 |
| 16 | Samad     | Patel    | 1-3     |       2 |       7 |
| 17 | Raees     | Syed     | 1-4     |       1 |       8 |
| 18 | Azmat     | Khan     | NULL    |       2 |       9 |
| 19 | Imran     | Khan     | NULL    |       2 |       9 |
+----+-----------+----------+---------+---------+---------+
+4

CTE, , :

;WITH T AS
(
    SELECT
        *,      
        ROW_NUMBER() OVER (ORDER BY ID) AS SrNo,
        ROW_NUMBER() OVER (PARTITION BY LastName,HouseNo ORDER BY HouseNo) AS PartNo        
    FROM MYTABLE
),
X as
 (
    SELECT 
        T.LastName,
        T.HouseNo,  
        (MAX(T.ID)-MIN(T.ID))+1 AS NoOfCount,        
         ROW_NUMBER() OVER(Order BY  MAX(ID)) AS RowNo,
         MAX(ID) AS ID       
    FROM T
    GROUP BY T.LastName,T.HouseNo, (T.SrNo - T.PartNo)      
)

Update MYTABLE
SET 
    MyCount=X.NoOfCount,
    CountId=X.RowNo
FROM X
WHERE MYTABLE.LastName=X.LastName 
AND MYTABLE.HouseNo=X.HouseNo 
AND MYTABLE.ID<=X.ID

SELECT * FROM MYTABLE

:

enter image description here

+3

declare @temp table (id int, firstname varchar(5), lastname varchar(5), houseno varchar(5), mycount int)

insert into @temp values(1,   'A',  'C',  '1-1',  2)
insert into @temp values(2,   'B',  'C',  '1-1',  2)
insert into @temp values(4,   'D',  'A',   null,  3)
insert into @temp values(5,   'F',  'A',   null,  3)
insert into @temp values(6,   'J',  'A',   null,  3)
insert into @temp values(7,   'Q',  'X',  '1-2',  3)
insert into @temp values(8,   'D',  'X',  '1-2',  3)
insert into @temp values(9,   'D',  'X',  '1-2',  3)
insert into @temp values(10,  'A',  'C',  '1-3',  3)
insert into @temp values(11,  'B',  'C',  '1-3',  3)
insert into @temp values(12,  'C',  'C',  '1-3',  3)
insert into @temp values(14,  'F',  'K',   null,  2)
insert into @temp values(15,  'J',  'K',   null,  2)
insert into @temp values(16,  'Q',  'X',  '1-5',  1)  

select count(ID) as _count 
from @temp
group by isnull(lastname, ''), isnull(houseno, '') 


_count
   3    
   2    
   2    
   3    
   3    
   1

:

select distinct
       t.lastname, 
       isnull(t.houseno, '') as houseno,
       (select count(ID) from @temp t2 where t2.lastname = t.lastname and t2.houseno = t.houseno) as _count_filled,
       (select count(ID) from @temp t2 where t2.lastname = t.lastname and isnull(t2.houseno, '') = isnull(t.houseno, '') and t2.houseno is null) as _count_empty
from   @temp t

:

lastname    houseno _count_filled   _count_empty    
A                   0               3   
C           1-1     2               0   
C           1-3     3               0   
K                   0               2   
X           1-2     3               0   
X           1-5     1               0
+2
SELECT COUNT(ID) AS _COUNT 
FROM MYTABLE 
GROUP BY ISNULL(LASTNAME, ''), ISNULL(HOUSENO, '');
+1

, , - MYCOUNT . , .

UPDATE MYTABLE T1
   SET T1.MYCOUNT =
    ( SELECT COUNT (*)
        FROM MYTABLE T2
        WHERE T1.LASTNAME = B2.LASTNAME
        AND NVL (T2.HOUSENO, 0) = NVL (T1.HOUSENO, 0)
        GROUP BY T2.LASTNAME, T2.HOUSENO);

* . Oracle SQL

+1

@ , . , ( SQL Server 2012)

--drop table #temp
create table  #temp  (id int, firstname varchar(15), lastname varchar(15), houseno varchar(5));
go
insert into #temp (id, firstname, lastname, houseno)
values
(1   , 'Imran'       ,'Khan'        ,'1-1')         
,(2   , 'Waseem'      ,'Khan'        ,'1-1')         
,(3   , 'Rihan'       ,'Khan'        ,'1-1')         
,(4   , 'Moiz'        ,'Shaikh'      ,'1-2')         
,(5   , 'Zbair'       ,'Shaikh'      ,'1-2')         
,(6   , 'Sultan'      ,'Shaikh'      ,'1-2')         
,(7   , 'Zaid'        ,'Khan'        , null)         
,(10  , 'Parvez'      ,'Patel'       ,'1-3')         
,(11  , 'Ahmed'       ,'Patel'       ,'1-3')         
,(12  , 'Rahat'       ,'Syed'        ,'1-4')         
,(13  , 'Talha'       ,'Khan'        ,null )         
,(14  , 'Zia'         ,'Khan'        ,null )         
,(15  , 'Arshad'      ,'Patel'       ,'1-3')         
,(16  , 'Samad'       ,'Patel'       ,'1-3')         
,(17  , 'Raees'       ,'Syed'        ,'1-4')         
,(18  , 'Azmat'       ,'Khan'        , null)      
,(19  , 'Imran'       ,'Khan'        , null)
 
-- query
; with c as (
select id, firstname, lastname, houseno=isnull(houseno, '')
, new_id=row_number() over (partition by lastname, isnull(houseno, '') order by id)
, grp = id -row_number() over (partition by lastname, isnull(houseno, '') order by id)
FROM #temp 
)
, d as (
select id, firstname, lastname, houseno, T.cnt, c.grp
, row_id=id-row_number() over ( partition by grp, houseno order by c.grp)
from c
cross apply (select cnt=count(*) from c as c2 where c.grp = c2.grp and c.lastname=c2.lastname and c.houseno=c2.houseno) T(cnt)
)
select id, FirstName, LastName, Houseno, MyCount=cnt,  CountId= DENSE_RANK() over (order by row_id)
from d

:

enter image description here

+1

.

CREATE  VIEW cnt  AS 
SELECT
    T.LastName,
    T.HouseNo,  MIN(t.id)  AS START , MAX(T.id) AS finish , 
    (MAX(T.ID)-MIN(T.ID))+1 AS NoOfCount,        
     ROW_NUMBER() OVER(Order BY  MAX(T.ID)) AS RowNo,
     MAX(T.ID) AS ID       
FROM (
SELECT
    *,      
    ROW_NUMBER() OVER (ORDER BY ID) AS SrNo,
    ROW_NUMBER() OVER (PARTITION BY LastName,HouseNo ORDER BY HouseNo) AS PartNo        
FROM myTable
) T 
GROUP BY T.LastName,T.HouseNo, (T.SrNo - T.PartNo)

:

SELECT a.*,
       b.NoOfCount,
       b.RowNo
FROM   myTable         AS a
       INNER JOIN cnt  AS b
            ON  a.id BETWEEN b.start AND b.finish

:

enter image description here

+1

:

SELECT COUNT (ID) AS _COUNT FROM MYTABLE GROUP LASTNAME + ISNULL (HOUSENO, '')

0

Source: https://habr.com/ru/post/1656692/

More articles:


All Articles