Query table

1

I have an input table
input table:

image
image742×510 104 KB

I want to count each client in each date has listened to how many distinct songs?
for example client 1 in 10/08/2016 has listened to 2 distinct songs (Song id: 3022,9857).

2

Kindly post the create table script with sample data.

1 Like
3

This is part of input table
PLAY_ID SONG_ID CLIENT_ID PLAY_TS
44BB190BC2493964E053CF0A000AB546 6164 1 09/08/2016 09:16:00
44BB190BC24A3964E053CF0A000AB546 544 3 10/08/2016 13:54:00
44BB190BC24B3964E053CF0A000AB546 9648 3 08/08/2016 06:08:00
44BB190BC24C3964E053CF0A000AB546 7565 2 10/08/2016 17:30:00
44BB190BC24D3964E053CF0A000AB546 8995 1 11/08/2016 02:40:00
44BB190BC24E3964E053CF0A000AB546 4407 1 08/08/2016 07:30:00
44BB190BC24F3964E053CF0A000AB546 5839 2 10/08/2016 02:40:00
44BB190BC2503964E053CF0A000AB546 548 3 09/08/2016 20:45:00
44BB190BC2513964E053CF0A000AB546 376 3 10/08/2016 04:57:00
44BB190BC2523964E053CF0A000AB546 3403 2 08/08/2016 21:14:00
44BB190BC2533964E053CF0A000AB546 7256 2 10/08/2016 06:29:00
44BB190BC2543964E053CF0A000AB546 4291 3 08/08/2016 09:26:00
44BB190BC2553964E053CF0A000AB546 5722 2 08/08/2016 23:33:00
44BB190BC2563964E053CF0A000AB546 9857 1 10/08/2016 22:05:00
44BB190BC2573964E053CF0A000AB546 3122 2 09/08/2016 08:35:00
44BB190BC2583964E053CF0A000AB546 217 2 10/08/2016 13:20:00
44BB190BC2593964E053CF0A000AB546 3022 1 10/08/2016 17:06:00
44BB190BC25A3964E053CF0A000AB546 9857 1 10/08/2016 15:06:00
44BB190BC25B3964E053CF0A000AB546 2168 3 11/08/2016 13:30:33

4 
CREATE table #sample(playsongid int) --etc all columns

Insert into #sample
Values
5 
select 
    Client_ID
  , cast(Play_TS as date)
  , count(distinct Song_ID) 
from 
   Table  
group by 
    Client_ID
  , cast(Play_TS as date)
6 
use [AdventureWorks2019]
go 

----------------------------------------------------------------
-- drop tables SQLTeam schema 

EXEC sp_MSforeachtable  @command1 = 'DROP TABLE ?', @whereand = 'AND SCHEMA_NAME(schema_id) = ''SQLTeam'' '

----------------------------------------------------------------
-- Create Tables 
create table SQLTeam.Input(Play_ID varchar(10),Song_ID int ,Client_ID int ,Play_TS datetime)

----------------------------------------------------------------
-- Insert data Tables

insert into SQLTeam.Input select 'aa',1,1,'2022-08-01 17:10'
insert into SQLTeam.Input select 'aa',1,1,'2022-07-15 21:00'
insert into SQLTeam.Input select 'aa',1,1,'2022-08-01 13:00'

insert into SQLTeam.Input select 'bb',2,2,'2022-08-01 17:10'
insert into SQLTeam.Input select 'bb',2,2,'2022-08-15 21:00'
insert into SQLTeam.Input select 'cc',3,2,'2022-08-01 13:00'

----------------------------------------------------------------
-- select tables SQLTeam schema 

EXEC sp_MSforeachtable  @command1 = 'SELECT *FROM  ?', @whereand = 'AND SCHEMA_NAME(schema_id) = ''SQLTeam'' '

----------------------------------------------------------------
-- Actual Query 
select 
    Client_ID
  , cast(Play_TS as date)
  , count(distinct Song_ID) 
from 
   Table  
group by 
    Client_ID
  , cast(Play_TS as date)