Hello,
I would like to query an ms-sql table with the following:
Show only costumers BETWEEN 2 dates
whos count of Product A BETWEEN 2 and 4
AND
count of Product B BETWEEN 6 and 8
AND
count of Product C BETWEEN 10 and 12
AND NOT
count of Product D BETWEEN 13 to 15
How can I achieve this in SQL?
I don't know where to start nesting it
Final result should be:
Costumer Name | Product A | Count A | Product B | Count B | Product C | Count C | Product D | Count D |
Johnny | TV | 3 | PC | 7 | RADIO | 11 | MONITOR | 14 |
You may need to use Pivot in your query.
Post the create table scripts with sample data.
Hi
I have something like this ?? is this OKAY
THERE is MORE Work
WHere CLaUSE needs filters .. AND LOGIC
Create Sample Data
USE tempdb
go
drop table #data
go
create table #data
(
customerName varchar(100),
custDate date ,
product varchar(100)
)
go
insert into #data select 'John','2018-10-09','TV'
insert into #data select 'John','2018-04-09','PC'
insert into #data select 'John','2018-10-13','RADIO'
insert into #data select 'John','2018-09-09','RADIO'
insert into #data select 'John','2018-10-11','TV'
insert into #data select 'John','2018-10-11','TV'
insert into #data select 'John','2018-06-11','RADIO'
insert into #data select 'John','2018-01-11','PC'
insert into #data select 'John','2018-09-11','TV'
insert into #data select 'John','2018-03-13','Monitor'
insert into #data select 'John','2018-10-11','TV'
insert into #data select 'John','2018-04-16','Monitor'
go
Data How it Looks IMAGE
SQL
SELECT customername,
'TV' AS 'TV',
Sum(CASE
WHEN product = 'TV' THEN 1
ELSE 0
END) AS CountTV,
'PC' AS 'PC',
Sum(CASE
WHEN product = 'PC' THEN 1
ELSE 0
END) AS CountPC,
'RADIO' AS 'RADIO',
Sum(CASE
WHEN product = 'RADIO' THEN 1
ELSE 0
END) AS CountRADIO,
'Monitor' AS 'Monitor',
Sum(CASE
WHEN product = 'Monitor' THEN 1
ELSE 0
END) AS CountMonitor
FROM #data
WHERE custdate BETWEEN '2018-01-01' AND '2018-12-12'
GROUP BY customername
go
Result
1 Like
wow!
First, Thank you very much Harish!
the "Create table" and "SQL" works perfectly fine and I get the same results.
the "NEW SQL with where clause logic" gives an error 1 line after this:
WHERE custdate BETWEEN '2018-01-01' AND '2018-12-12'
Error: "An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference."
Hi
I have changed the NEW SQL code
Here is the code please try it
New SQL Code .. changed
SELECT customername,
product,
Count(*) AS productCount
FROM #data
WHERE custdate BETWEEN '2018-01-01' AND '2018-12-12'
GROUP BY customername,
product
HAVING product = 'TV'
AND Count(*) BETWEEN 2 AND 4
OR product = 'PC'
AND Count(*) BETWEEN 6 AND 8
OR product = 'RADIO'
AND Count(*) BETWEEN 10 AND 12
OR product = 'Monitor'
AND Count(*) NOT BETWEEN 13 AND 15
Hey!
Thanks for your reply.
What you just sent is where I got stuck in the first place.
the 'Having' clause OR's are generating customers who bought X to Y TV's OR from X to Y RADIO's
the condition should be AND
the first NEW SQL code was perfect but giving an error when adding multiple AND's after the WHERE part.
Is it possible to implement a solution on that?
Output should be single line per customer.
Show only if matches all criteria (all relationships are AND)
like you mentioned:
I mean example
Count of PC 6
And
Count of TV 7
What this will do is
If both conditions match then output displayed
Example for final result:
Customer John answered all Criterias:
Please see this final SQL
SQL Final
SELECT a.*
FROM (SELECT customername,
'TV' AS 'TV',
Sum(CASE
WHEN product = 'TV' THEN 1
ELSE 0
END) AS CountTV,
'PC' AS 'PC',
Sum(CASE
WHEN product = 'PC' THEN 1
ELSE 0
END) AS CountPC,
'RADIO' AS 'RADIO',
Sum(CASE
WHEN product = 'RADIO' THEN 1
ELSE 0
END) AS CountRADIO,
'Monitor' AS 'Monitor',
Sum(CASE
WHEN product = 'Monitor' THEN 1
ELSE 0
END) AS CountMonitor
FROM #data
WHERE custdate BETWEEN '2018-01-01' AND '2018-12-12'
GROUP BY customername) a
WHERE (a.counttv BETWEEN 2 AND 4 )
AND (a.countpc BETWEEN 6 AND 8)
AND (a.countradio BETWEEN 10 AND 12)
AND (a.countmonitor NOT BETWEEN 13 AND 15)
