December 7, 2017 at 7:36 am
Guys, how do i make the following only bring back one value for each REGNO? As you can see some of then are bring back 2 in the total column? Select Distinct shows the same results.
Many thanks
SELECT
L.Dealer
,Count(*) as [Total]
,l.RegNo
FROM LogFile l
JOIN UpsellLog U ON U.LogSeqNo=L.Seq
WHERE Created >='2017-12-01' and Created <'2017-12-07'
AND U.UpsellId ='106' --106 IS BEMIS
AND l.Tran1 in ('IBB', 'OBB', 'W3B', 'ENQ')
GROUP BY
L.Dealer
,l.RegNo
ORDER BY l.Dealer
December 7, 2017 at 8:36 am
craig.jenkins - Thursday, December 7, 2017 7:36 AMGuys, how do i make the following only bring back one value for each REGNO? As you can see some of then are bring back 2 in the total column? Select Distinct shows the same results.
As we can see? We can 't run your query, so no, we can't see. We aren't mind readers 🙂
Could you please post DDL and Consumable Sample Data, as well as your expected output?
Thanks.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 7, 2017 at 8:48 am
Thom A - Thursday, December 7, 2017 8:36 AMcraig.jenkins - Thursday, December 7, 2017 7:36 AMGuys, how do i make the following only bring back one value for each REGNO? As you can see some of then are bring back 2 in the total column? Select Distinct shows the same results.As we can see? We can 't run your query, so no, we can't see. We aren't mind readers 🙂
Could you please post DDL and Consumable Sample Data, as well as your expected output?
Thanks.
+ 100 was mid post when you replied Thom, the OP needs to post DDL and DML! Craig?
...
December 7, 2017 at 8:57 am
December 7, 2017 at 9:05 am
What do you mean by results? Is that your expected results? We need your Sample Data and DDL to be able to show you how to achieve that. Like i said before, we don't have access to your data (as we shoulnd't, as those are VRNs!) so you need to supply us with (obfuscated) data we can work with.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 7, 2017 at 9:14 am
sorry what is meant by DDL and DML?
December 7, 2017 at 9:17 am
craig.jenkins - Thursday, December 7, 2017 9:14 AMsorry what is meant by DDL and DML?
take a look here on advice for posting
https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
December 7, 2017 at 9:41 am
Thanks all, sample data below. Hoping to achieve - If the ref is duplicated it only counts it once rather than 3 (as in the example below)
-- Sample data
IF OBJECT_ID('tempdb..#SampleData') IS NOT NULL DROP TABLE #SampleData
SELECT * INTO #SampleData FROM (VALUES
('BA', '12345'),
('BA', '12345'),
('BA', '12345'),
('BA', '12346')
) d (Name, ref)
SELECT NAME, COUNT(*) AS TOTAL , Ref
FROM #SampleData
GROUP BY Name, REF
December 7, 2017 at 9:47 am
craig.jenkins - Thursday, December 7, 2017 9:41 AMThanks all, sample data below. Hoping to achieve - If the ref is duplicated it only counts it once rather than 3 (as in the example below)
-- Sample data
IF OBJECT_ID('tempdb..#SampleData') IS NOT NULL DROP TABLE #SampleData
SELECT * INTO #SampleData FROM (VALUES
('BA', '12345'),
('BA', '12345'),
('BA', '12345'),
('BA', '12346')) d (Name, ref)
SELECT NAME, COUNT(*) AS TOTAL , Ref
FROM #SampleData
GROUP BY Name, REF
Wait, you just want to count the DISTINCT references? Instead of using COUNT(*) then use COUNT(DISTINCT Ref).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 7, 2017 at 10:13 am
Thom A - Thursday, December 7, 2017 9:47 AMcraig.jenkins - Thursday, December 7, 2017 9:41 AMThanks all, sample data below. Hoping to achieve - If the ref is duplicated it only counts it once rather than 3 (as in the example below)
-- Sample data
IF OBJECT_ID('tempdb..#SampleData') IS NOT NULL DROP TABLE #SampleData
SELECT * INTO #SampleData FROM (VALUES
('BA', '12345'),
('BA', '12345'),
('BA', '12345'),
('BA', '12346')) d (Name, ref)
SELECT NAME, COUNT(*) AS TOTAL , Ref
FROM #SampleData
GROUP BY Name, REFWait, you just want to count the DISTINCT references? Instead of using COUNT(*) then use COUNT(DISTINCT Ref).
A distinct count on any of the grouping fields will always produce 1. You could just hard-code a 1 in there.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 7, 2017 at 2:23 pm
Just a quick note on this... using DISTINCT guarantees you only see one row of output per unique combination of values in the table rows for the specified columns. Not sure why you would need to then COUNT them. It only makes sense to count them if you need to know how many dupes there are, or if you have a third (or more) column with differing values per unique combo of the original columns, and you needed to know how many rows there were per combination. The danger in using COUNT to establish that there's only one row to represent things is that you are then potentially using data from the original table in a way that might still mess up the results if you forget to always reference that data using the DISTINCT keyword.
Take a quick look at the following code, which is redundant for the reasons I cited above, but helps illustrate how that's the case if you run the inner query separately after creating the temp table:-- Sample data
IF OBJECT_ID('tempdb..#SampleData') IS NOT NULL DROP TABLE #SampleData;
SELECT *
INTO #SampleData
FROM (
VALUES ('BA', '12345'),
('BA', '12345'),
('BA', '12345'),
('BA', '12346')
) AS d (Name, ref);
SELECT Name, Ref, COUNT(*) AS TOTAL
FROM (
SELECT DISTINCT Name, Ref
FROM #SampleData
) AS X
GROUP BY X.Name, X.Ref;
DROP TABLE #SampleData;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply