Counter settings: select

Showing posts with label select. Show all posts

Thursday, March 29, 2012

Count Records Between two dates

Hi all,

I've got a quick question.

How would I count the number of records between two dates.

I started with something like this.

SELECT COUNT(*) AS COUNT, dtAdded
FROM tSurveyPerson
WHERE (dtAdded BETWEEN '2004-03-01' AND '2004-04-01')
GROUP BY dtAdded

but as you probably all know this ain't right. I would like to get just the number of records.

ThanksLeave off the GROUP BY.

-PatP|||got it...

SELECT COUNT(dtAdded) AS numRecords
FROM tSurveyPerson
WHERE (dtAdded BETWEEN '2004-03-01' AND '2004-04-01')

Thanks Patsql

count of subqueries plz..

how to get count(*) of a subquery?
something like

select count(*) from
(select F1,F2,F3 from MyTable)

*above doesn't work.

i cannot change, that subquery, it's coming from somewhere else.

Thank You..

Add table alias:

select count(*) from (select F1,F2,F3 from MyTable) as T

Count of same column appearing twice in the query

SELECT CONVERT(char(10),min(dateadd(day, datediff(day,'19000101',d_datecreated)/7*7, '19000101')),101) as StartWeek,
CONVERT(char(10),max(dateadd(day, datediff(day,'19000101',dateadd(day,6,d_datecreated))/7*7, '19000101')),101) as EndWeek,
COUNT(*) AS MagazineAdCount
FROM orderformlineitems
where product_variant_id = '4010436709979469536'
group by datediff(day,'19000101',d_datecreated)/7
order by StartWeek ASC

Output
StartWeek EndWeek MagazineAdCount
04/16/2007 04/23/2007 8
04/23/2007 04/30/2007 15
04/30/2007 05/07/2007 5

SELECT CONVERT(char(10),min(dateadd(day, datediff(day,'19000101',d_datecreated)/7*7, '19000101')),101) as StartWeek,
CONVERT(char(10),max(dateadd(day, datediff(day,'19000101',dateadd(day,6,d_datecreated))/7*7, '19000101')),101) as EndWeek,
COUNT(*) AS BannerAdCount
FROM orderformlineitems
where product_variant_id = '7453910328410493551'
group by datediff(day,'19000101',d_datecreated)/7
order by StartWeek ASC

Output
StartWeek EndWeek BannerAdCount
04/16/2007 04/23/2007 15
04/23/2007 04/30/2007 21
04/30/2007 05/07/2007 22

I WANT THE BELOW OUTPUT THRU A SINGLE QUERY.....

StartWeek EndWeek MagazineAdCount BannerAdCount

04/16/2007 04/23/2007 8 15
04/23/2007 04/30/2007 15 21
04/30/2007 05/07/2007 5 22

Can anyone help please ?

Thanks

You can join these 2 query as single query..

Select Data1.Startweek,

Data1.EndWeek,

Data1.MagazineAdCount,

Data2.BannerAdCount

From

(SELECT CONVERT(char(10),min(dateadd(day, datediff(day,'19000101',d_datecreated)/7*7, '19000101')),101) as StartWeek,

CONVERT(char(10),max(dateadd(day, datediff(day,'19000101',dateadd(day,6,d_datecreated))/7*7, '19000101')),101) as EndWeek,

COUNT(*) AS MagazineAdCount

FROM orderformlineitems

where product_variant_id = '4010436709979469536'

group by datediff(day,'19000101',d_datecreated)/7 ) as Data1

Join

(SELECT CONVERT(char(10),min(dateadd(day, datediff(day,'19000101',d_datecreated)/7*7, '19000101')),101) as StartWeek,

CONVERT(char(10),max(dateadd(day, datediff(day,'19000101',dateadd(day,6,d_datecreated))/7*7, '19000101')),101) as EndWeek,

COUNT(*) AS BannerAdCount

FROM orderformlineitems

where product_variant_id = '7453910328410493551'

group by datediff(day,'19000101',d_datecreated)/7 ) as Data2 On Data1.StartWeek = Data2.StartWeek And Data1.EndWeek = Data2.EndWeek

|||Thank you !!|||

Use following query:

Code Snippet

SELECT CONVERT(char(10),min(dateadd(day, datediff(day,'19000101',d_datecreated)/7*7, '19000101')),101) as StartWeek,

CONVERT(char(10),max(dateadd(day, datediff(day,'19000101',dateadd(day,6,d_datecreated))/7*7, '19000101')),101) as EndWeek,

sum ( case product_variant_id when '4010436709979469536' then 1 else 0 end) AS MagazineAdCount,

sum ( case product_variant_id when '7453910328410493551' then 1 else 0 end) AS BannerAdCount

FROM orderformlineitems

where product_variant_id in ( '4010436709979469536', '7453910328410493551')

group by datediff(day,'19000101',d_datecreated)/7

order by StartWeek ASC

|||Thanks !!

Count of matching result sets

I'm okay with simple select statements, but this one is kicking my behind...
I'm trying to create an overview of our data holdings for various
categories. It needs to be dynamic as the types of data and the accompanying
criteria will be dynamic.
The data holdings table, which I'll call "DH", is related via a one-to-many
to an "Xref_DH_Topic" table, which is related via a many-to-one to a "Topics"
table.
"DH" table:
| dhid | Name |
| 1 | Fred's Ocean Carbon Data |
| 2 | Joe's Tide Data |
| 3 | Pete's Surface Flux Data |
| 4 | Lou's Tide & Surface Flux Data |
"Xref_DH_Topic" table:
| xid | dhid | topicid |
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 2 | 1 |
| 4 | 2 | 3 |
| 5 | 3 | 1 |
| 6 | 3 | 4 |
| 7 | 3 | 5 |
| 8 | 4 | 1 |
| 9 | 4 | 3 |
| 10 | 4 | 4 |
| 11 | 4 | 5 |
"Topics" table:
| topicid | TopicTitle |
| 1 | Ocean |
| 2 | Carbon |
| 3 | Tide |
| 4 | Surface |
| 5 | Flux |
So far so good. Now I want to have a "DH Matrix" table which defines the
criteria for inclusion in a particular row, which will be modified as more
data holdings and topics are added. It is linked to an "Xref_Matrix_Topic"
table which links to the same "Topics" table as above.
"DH Matrix" table:
| dhmid | Category |
| 1 | All Ocean Data |
| 2 | Tide Data Holdings |
| 3 | Carbon Data Holdings |
| 4 | Surface Flux Data Holdings |
"Xref_Matrix_Topic" table:
| xmtid | dhmid | topicid |
| 1 | 1 | 1 |
| 2 | 2 | 1 |
| 3 | 2 | 3 |
| 4 | 3 | 1 |
| 5 | 3 | 2 |
| 6 | 4 | 1 |
| 7 | 4 | 4 |
| 8 | 4 | 5 |
The desired end-result is a view that will display each "category" in the
"DH Matrix" table and count up the number of rows in the "DH" table that
match the criteria set up in the "Xref_Matrix_Topic" table.
Matrix of Data Holdings:
| Category | # of Hits in DH |
| All Ocean Data | 4 |
| Tide Data Holdings | 2 |
| Carbon Data Holdings | 1 |
| Surface Flux Holdings | 2 |
Any help would be greatly appreciated.
TIA.
What about some DDL scripts and data for us ?

Count of matching result sets

I'm okay with simple select statements, but this one is kicking my behind...
I'm trying to create an overview of our data holdings for various
categories. It needs to be dynamic as the types of data and the accompanyin
g
criteria will be dynamic.
The data holdings table, which I'll call "DH", is related via a one-to-many
to an "Xref_DH_Topic" table, which is related via a many-to-one to a "Topics
"
table.
"DH" table:
| dhid | Name |
| 1 | Fred's Ocean Carbon Data |
| 2 | Joe's Tide Data |
| 3 | Pete's Surface Flux Data |
| 4 | Lou's Tide & Surface Flux Data |
"Xref_DH_Topic" table:
| xid | dhid | topicid |
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 2 | 1 |
| 4 | 2 | 3 |
| 5 | 3 | 1 |
| 6 | 3 | 4 |
| 7 | 3 | 5 |
| 8 | 4 | 1 |
| 9 | 4 | 3 |
| 10 | 4 | 4 |
| 11 | 4 | 5 |
"Topics" table:
| topicid | TopicTitle |
| 1 | Ocean |
| 2 | Carbon |
| 3 | Tide |
| 4 | Surface |
| 5 | Flux |
So far so good. Now I want to have a "DH Matrix" table which defines the
criteria for inclusion in a particular row, which will be modified as more
data holdings and topics are added. It is linked to an "Xref_Matrix_Topic"
table which links to the same "Topics" table as above.
"DH Matrix" table:
| dhmid | Category |
| 1 | All Ocean Data |
| 2 | Tide Data Holdings |
| 3 | Carbon Data Holdings |
| 4 | Surface Flux Data Holdings |
"Xref_Matrix_Topic" table:
| xmtid | dhmid | topicid |
| 1 | 1 | 1 |
| 2 | 2 | 1 |
| 3 | 2 | 3 |
| 4 | 3 | 1 |
| 5 | 3 | 2 |
| 6 | 4 | 1 |
| 7 | 4 | 4 |
| 8 | 4 | 5 |
The desired end-result is a view that will display each "category" in the
"DH Matrix" table and count up the number of rows in the "DH" table that
match the criteria set up in the "Xref_Matrix_Topic" table.
Matrix of Data Holdings:
| Category | # of Hits in DH |
| All Ocean Data | 4 |
| Tide Data Holdings | 2 |
| Carbon Data Holdings | 1 |
| Surface Flux Holdings | 2 |
Any help would be greatly appreciated.
TIA.What about some DDL scripts and data for us ?

Tuesday, March 27, 2012

COUNT of datediff

Ok... it's stupid but I'm getting crazy...
SELECT DATEDIFF("D",ORD_DTRSC,BLT_DATA) AS DAYS
FROM BOLCLI INNER JOIN ORDCLI ON BOLCLI.BLT_NORD=ORDCLI.ORD_NUM AND
BOLCLI.BLT_PRORD = ORDCLI.ORD_PRG
WHERE ORD_DTRSC > 0
how can I have the COUNT() of the days grouped by positive and
negative?
suppose:
DAYS
5
3
1
11
-2
-3
0
I'd like to get
POSITIVE NEGATIVE
5 2
...it should be not difficult... I know...
...but...
thnx in advanceWorst...
I must get something like:
PERIOD POSITIVE NEGATIVE
1st quarter 2004 3 0
2nd quarter 2004 1 1
3rd quarter 2004 0 0
4th quarter 2004 1 1
...
...
:(|||hi
CREATE TABLE #Test
(
col INT NOT NULL
)
INSERT INTO #Test VALUES (1)
INSERT INTO #Test VALUES (10)
INSERT INTO #Test VALUES (12)
INSERT INTO #Test VALUES (-1)
INSERT INTO #Test VALUES (-10)
SELECT COUNT(CASE WHEN col>=0 THEN 1 END ) AS Positive,
COUNT(CASE WHEN col<0 THEN 1 END ) AS Negative
FROM #Test
"ugom" <ugomangini@.tiscali.it> wrote in message
news:1132760283.256957.100830@.o13g2000cwo.googlegroups.com...
> Ok... it's stupid but I'm getting crazy...
> SELECT DATEDIFF("D",ORD_DTRSC,BLT_DATA) AS DAYS
> FROM BOLCLI INNER JOIN ORDCLI ON BOLCLI.BLT_NORD=ORDCLI.ORD_NUM AND
> BOLCLI.BLT_PRORD = ORDCLI.ORD_PRG
> WHERE ORD_DTRSC > 0
> how can I have the COUNT() of the days grouped by positive and
> negative?
> suppose:
> DAYS
> 5
> 3
> 1
> 11
> -2
> -3
> 0
> I'd like to get
> POSITIVE NEGATIVE
> 5 2
> ...it should be not difficult... I know...
> ...but...
> thnx in advance
>|||Try
SELECT
COUNT(CASE WHEN DATEDIFF("D",ORD_DTRSC,BLT_DATA) > 0 Then 1 END) as
Positive,
COUNT(CASE WHEN DATEDIFF("D",ORD_DTRSC,BLT_DATA)< 0 Then 1 END) as
Negative
FROM YourTable
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"ugom" <ugomangini@.tiscali.it> wrote in message
news:1132760283.256957.100830@.o13g2000cwo.googlegroups.com...
> Ok... it's stupid but I'm getting crazy...
> SELECT DATEDIFF("D",ORD_DTRSC,BLT_DATA) AS DAYS
> FROM BOLCLI INNER JOIN ORDCLI ON BOLCLI.BLT_NORD=ORDCLI.ORD_NUM AND
> BOLCLI.BLT_PRORD = ORDCLI.ORD_PRG
> WHERE ORD_DTRSC > 0
> how can I have the COUNT() of the days grouped by positive and
> negative?
> suppose:
> DAYS
> 5
> 3
> 1
> 11
> -2
> -3
> 0
> I'd like to get
> POSITIVE NEGATIVE
> 5 2
> ...it should be not difficult... I know...
> ...but...
> thnx in advance
>|||Wonderful!
...but, what about grouping by period...?
thnx again|||I've Found!
SELECT
MYYEAR, MYQUARTER,
COUNT(CASE WHEN query1.MYDAYS>=0 THEN 1 END ) AS Positive,
COUNT(CASE WHEN query1.MYDAYS<0 THEN 1 END ) AS Negative
FROM
(SELECT YEAR(BOLCLI.BLT_DATA) AS MYYEAR,
DATEPART("qq",BOLCLI.BLT_DATA) AS MYQUARTER,
DATEDIFF("D",ORD_DTRSC,BLT_DATA) AS MYDAYS
FROM BOLCLI INNER JOIN ORDCLI ON BOLCLI.BLT_NORD=ORDCLI.ORD_NUM AND
BOLCLI.BLT_PRORD = ORDCLI.ORD_PRG
WHERE ORD_DTRSC > 0) AS QUERY1
GROUP BY MYYEAR,MYQUARTER
thnx again to all of you!

Count of Counts in SQL Server

Hi folks,

I have the following query to run:

select count(
select count(*)
from student
group by firstName
having count(*) > 1)

Basically, I first need to get a count of all students that have the same first name, then I need total count of all those students. How can I change the above query to get the result I need?

Thanks so much!

-Parulthat doesn't make any sense

a count of a count will always be 1|||why is that so? how do we get a count of all those rows that appear in the inner query?|||okay, let's break this problem down into steps

could you please run the inner query all by itself and show me what you get|||Since the inner select query has a group by field, the number of rows it returned may be more than one also.|||it shows you different counts by student first name; the result looks like:
count
--
5
24
2
23
2|||ah yes, okay, then i think what you want is this --select count(*)
from (
select count(*)
from student
group by firstName
having count(*) > 1
) as counts|||Exactly, but this is giving me the following error:

No column was specified for column 1 of 'counts'.|||I got it! The inner select column needs an alias.
select count(*)
from (
select count(*) as 'cnt'
from student
group by firstName
having count(*) > 1
) as counts

Thanks so much for your help!

Count Item function

Hi,

I need to count the number of rows in my Item table. The following statement gives me the number i.e. 200.

Select Count(*) As Counter
From Item
Group By Id

How can I number each individual row so that the row will have a number next to it i.e.

Select Count(*) As Counter,[Count Statement] as Number of the Row
From Item
Group By Id

Thanks

Is this what you want?

SELECT Row_NUMBER() OVER(Order by a.id) as series_No, a.id, b.myCount

FROM items a

LEFT JOIN (SELECT COUNT(*) as myCount, id

FROM items

group by id) b ON a.id=b.id

|||

I am not sure, I get the following error.

Server: Msg 195, Level 15, State 10, Line 1
'Row_NUMBER' is not a recognized function name.
Server: Msg 170, Level 15, State 1, Line 9
Line 9: Incorrect syntax near 'b'.

|||

Row_Number() is a new function in SQL Server 2005.

Try this one:

SELECT (select count(*) from items as t2

where t2.items <= t1.items )+1 as series_No, t1.id

FROM items t1

ORDER BY t1.items

|||Thanks, that helped.|||You should actually do this on the client-side where it is easier and it will perform better. Simply return the rows in a sorted manner and then number them on the client side.

Count Item function

Hi,

I need to count the number of rows in my Item table. The following statement gives me the number i.e. 200.

Select Count(*) As Counter
From Item
Group By Id

How can I number each individual row so that the row will have a number next to it i.e.

Select Count(*) As Counter,[Count Statement] as Number of the Row
From Item
Group By Id

Thanks

Is this what you want?

SELECT Row_NUMBER() OVER(Order by a.id) as series_No, a.id, b.myCount

FROM items a

LEFT JOIN (SELECT COUNT(*) as myCount, id

FROM items

group by id) b ON a.id=b.id

|||

I am not sure, I get the following error.

Server: Msg 195, Level 15, State 10, Line 1
'Row_NUMBER' is not a recognized function name.
Server: Msg 170, Level 15, State 1, Line 9
Line 9: Incorrect syntax near 'b'.

|||

Row_Number() is a new function in SQL Server 2005.

Try this one:

SELECT (select count(*) from items as t2

where t2.items <= t1.items )+1 as series_No, t1.id

FROM items t1

ORDER BY t1.items

Sunday, March 25, 2012

COUNT function not working with GROUP BY

The following SQL statement returns 38 records:
SELECT Base_Contacts.ID_Contact AS CONTACTS
FROM Base_Contacts LEFT OUTER JOIN
Base_ContactAttributes ON Base_Contacts.ID_Contact = Base_ContactAttributes.ID_Contact
WHERE (Base_ContactAttributes.ID_Level_Value IN (10150, 10153))
GROUP BY Base_Contacts.ID_Contact
HAVING (COUNT(DISTINCT Base_ContactAttributes.ID_Level_Value) = 2)
Rather than retrieving the recordset, I want to simply get a count. My
syntax must be wrong (same syntax as above but I added COUNT) because the
following query simply returns 38 records with the value "1":
SELECT COUNT(DISTINCT Base_Contacts.ID_Contact) AS CONTACTS
FROM Base_Contacts LEFT OUTER JOIN
Base_ContactAttributes ON Base_Contacts.ID_Contact = Base_ContactAttributes.ID_Contact
WHERE (Base_ContactAttributes.ID_Level_Value IN (10150, 10153))
GROUP BY Base_Contacts.ID_Contact
HAVING (COUNT(DISTINCT Base_ContactAttributes.ID_Level_Value) = 2)
Any suggestions for this beginner? Thanks!Hi
GROUP BY always returns 1 row for each different value of the GROUP BY
column, so you're still getting one row for each different
Base_Contacts.ID_Contact
value. If you want the count of all values, don't use GROUP BY.
I'm not exactly sure what you want to do with
Base_ContactAttributes.ID_Level_Value in the new query. You'll need to give
us more info, and maybe even some sample data and sample output, or at least
an explanation of what you're doing with that value if you want the rest of
the solution.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"gstoa" <gstoa@.discussions.microsoft.com> wrote in message
news:FC26E396-3FFF-408E-B326-F5DA12FACE3B@.microsoft.com...
> The following SQL statement returns 38 records:
> SELECT Base_Contacts.ID_Contact AS CONTACTS
> FROM Base_Contacts LEFT OUTER JOIN
> Base_ContactAttributes ON Base_Contacts.ID_Contact => Base_ContactAttributes.ID_Contact
> WHERE (Base_ContactAttributes.ID_Level_Value IN (10150, 10153))
> GROUP BY Base_Contacts.ID_Contact
> HAVING (COUNT(DISTINCT Base_ContactAttributes.ID_Level_Value) = 2)
> Rather than retrieving the recordset, I want to simply get a count. My
> syntax must be wrong (same syntax as above but I added COUNT) because the
> following query simply returns 38 records with the value "1":
> SELECT COUNT(DISTINCT Base_Contacts.ID_Contact) AS CONTACTS
> FROM Base_Contacts LEFT OUTER JOIN
> Base_ContactAttributes ON Base_Contacts.ID_Contact => Base_ContactAttributes.ID_Contact
> WHERE (Base_ContactAttributes.ID_Level_Value IN (10150, 10153))
> GROUP BY Base_Contacts.ID_Contact
> HAVING (COUNT(DISTINCT Base_ContactAttributes.ID_Level_Value) = 2)
> Any suggestions for this beginner? Thanks!|||Remove your GROUP BY clause. You're counting the number in every group of
ID_Contacts.
"gstoa" <gstoa@.discussions.microsoft.com> wrote in message
news:FC26E396-3FFF-408E-B326-F5DA12FACE3B@.microsoft.com...
> The following SQL statement returns 38 records:
> SELECT Base_Contacts.ID_Contact AS CONTACTS
> FROM Base_Contacts LEFT OUTER JOIN
> Base_ContactAttributes ON Base_Contacts.ID_Contact => Base_ContactAttributes.ID_Contact
> WHERE (Base_ContactAttributes.ID_Level_Value IN (10150, 10153))
> GROUP BY Base_Contacts.ID_Contact
> HAVING (COUNT(DISTINCT Base_ContactAttributes.ID_Level_Value) = 2)
> Rather than retrieving the recordset, I want to simply get a count. My
> syntax must be wrong (same syntax as above but I added COUNT) because the
> following query simply returns 38 records with the value "1":
> SELECT COUNT(DISTINCT Base_Contacts.ID_Contact) AS CONTACTS
> FROM Base_Contacts LEFT OUTER JOIN
> Base_ContactAttributes ON Base_Contacts.ID_Contact => Base_ContactAttributes.ID_Contact
> WHERE (Base_ContactAttributes.ID_Level_Value IN (10150, 10153))
> GROUP BY Base_Contacts.ID_Contact
> HAVING (COUNT(DISTINCT Base_ContactAttributes.ID_Level_Value) = 2)
> Any suggestions for this beginner? Thanks!|||Here's some sample data:
CREATE TABLE mypeople
(
id_person int IDENTITY (1, 1) NOT NULL,
name varchar(10) NULL
)
CREATE TABLE myattributes
(
id_person int NOT NULL,
id_attribute int NOT NULL
) ON [PRIMARY]
INSERT INTO mypeople VALUES('John')
INSERT INTO mypeople VALUES('Bill')
INSERT INTO mypeople VALUES('Jane')
INSERT INTO myattributes VALUES(1,1000)
INSERT INTO myattributes VALUES(1,1002)
INSERT INTO myattributes VALUES(1,1004)
INSERT INTO myattributes VALUES(1,1006)
INSERT INTO myattributes VALUES(1,1008)
INSERT INTO myattributes VALUES(2,1002)
INSERT INTO myattributes VALUES(2,1004)
INSERT INTO myattributes VALUES(3,1004)
Now my count distinct query is as follows:
SELECT COUNT(DISTINCT dbo.mypeople.name) AS Expr1
FROM dbo.mypeople INNER JOIN
dbo.myattributes ON dbo.mypeople.id_person =dbo.myattributes.id_person
WHERE (dbo.myattributes.id_attribute IN (1002, 1004))
HAVING (COUNT(DISTINCT dbo.myattributes.id_attribute) = 2)
Notice that the count returned from this query is 3. Unless I'm not
understanding the query logic correctly, I would have thought that only two
people met the IN/HAVING clause criteria. In this example, only John and
Bill have attribute values of both 1002 and 1004. I would have thought that
this
query would have returned the value of 2 -- not 3 '
"Kalen Delaney" wrote:
> Hi
> GROUP BY always returns 1 row for each different value of the GROUP BY
> column, so you're still getting one row for each different
> Base_Contacts.ID_Contact
> value. If you want the count of all values, don't use GROUP BY.
> I'm not exactly sure what you want to do with
> Base_ContactAttributes.ID_Level_Value in the new query. You'll need to give
> us more info, and maybe even some sample data and sample output, or at least
> an explanation of what you're doing with that value if you want the rest of
> the solution.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "gstoa" <gstoa@.discussions.microsoft.com> wrote in message
> news:FC26E396-3FFF-408E-B326-F5DA12FACE3B@.microsoft.com...
> > The following SQL statement returns 38 records:
> >
> > SELECT Base_Contacts.ID_Contact AS CONTACTS
> > FROM Base_Contacts LEFT OUTER JOIN
> > Base_ContactAttributes ON Base_Contacts.ID_Contact => > Base_ContactAttributes.ID_Contact
> > WHERE (Base_ContactAttributes.ID_Level_Value IN (10150, 10153))
> > GROUP BY Base_Contacts.ID_Contact
> > HAVING (COUNT(DISTINCT Base_ContactAttributes.ID_Level_Value) = 2)
> >
> > Rather than retrieving the recordset, I want to simply get a count. My
> > syntax must be wrong (same syntax as above but I added COUNT) because the
> > following query simply returns 38 records with the value "1":
> >
> > SELECT COUNT(DISTINCT Base_Contacts.ID_Contact) AS CONTACTS
> > FROM Base_Contacts LEFT OUTER JOIN
> > Base_ContactAttributes ON Base_Contacts.ID_Contact => > Base_ContactAttributes.ID_Contact
> > WHERE (Base_ContactAttributes.ID_Level_Value IN (10150, 10153))
> > GROUP BY Base_Contacts.ID_Contact
> > HAVING (COUNT(DISTINCT Base_ContactAttributes.ID_Level_Value) = 2)
> >
> > Any suggestions for this beginner? Thanks!
>
>|||Thanks for the nice script. In your case, the count in the SELECT list is
counting the number of people who have any rows returned with values of
either 1002 or 1004 and so that is all the people, 3 of them.
I'm not sure exactly what the HAVING is doing here, but I do not think it
means what you think it means. I'll have to think about it to figure out
what it means here, or I'll just ask Itzik. :-)
In the meantime, you can rewrite this. You really don't need the mypeople
table at all, since you don't need any info that is just in that table. This
query gives you the list of id_person values that have exactly the two
attributes you need:
SELECT dbo.myattributes.id_person, COUNT(*) AS Expr1
FROM dbo.myattributes
WHERE (dbo.myattributes.id_attribute IN (1002, 1004))
GROUP BY dbo.myattributes.id_person
HAVING COUNT(*) = 2
So, we can just make that a derived table, and count the rows in it:
SELECT count(*) FROM
(SELECT dbo.myattributes.id_person, COUNT(*) AS Expr1
FROM dbo.myattributes
WHERE (dbo.myattributes.id_attribute IN (1002, 1004))
GROUP BY dbo.myattributes.id_person
HAVING COUNT(*) = 2) AS counts
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"gstoa" <gstoa@.discussions.microsoft.com> wrote in message
news:DB1E33DD-5BE0-46A4-8511-668EC549DC7D@.microsoft.com...
> Here's some sample data:
> CREATE TABLE mypeople
> (
> id_person int IDENTITY (1, 1) NOT NULL,
> name varchar(10) NULL
> )
> CREATE TABLE myattributes
> (
> id_person int NOT NULL,
> id_attribute int NOT NULL
> ) ON [PRIMARY]
> INSERT INTO mypeople VALUES('John')
> INSERT INTO mypeople VALUES('Bill')
> INSERT INTO mypeople VALUES('Jane')
> INSERT INTO myattributes VALUES(1,1000)
> INSERT INTO myattributes VALUES(1,1002)
> INSERT INTO myattributes VALUES(1,1004)
> INSERT INTO myattributes VALUES(1,1006)
> INSERT INTO myattributes VALUES(1,1008)
> INSERT INTO myattributes VALUES(2,1002)
> INSERT INTO myattributes VALUES(2,1004)
> INSERT INTO myattributes VALUES(3,1004)
> Now my count distinct query is as follows:
> SELECT COUNT(DISTINCT dbo.mypeople.name) AS Expr1
> FROM dbo.mypeople INNER JOIN
> dbo.myattributes ON dbo.mypeople.id_person => dbo.myattributes.id_person
> WHERE (dbo.myattributes.id_attribute IN (1002, 1004))
> HAVING (COUNT(DISTINCT dbo.myattributes.id_attribute) = 2)
> Notice that the count returned from this query is 3. Unless I'm not
> understanding the query logic correctly, I would have thought that only
two
> people met the IN/HAVING clause criteria. In this example, only John and
> Bill have attribute values of both 1002 and 1004. I would have thought
that
> this
> query would have returned the value of 2 -- not 3 '
>
> "Kalen Delaney" wrote:
> > Hi
> >
> > GROUP BY always returns 1 row for each different value of the GROUP BY
> > column, so you're still getting one row for each different
> > Base_Contacts.ID_Contact
> > value. If you want the count of all values, don't use GROUP BY.
> >
> > I'm not exactly sure what you want to do with
> > Base_ContactAttributes.ID_Level_Value in the new query. You'll need to
give
> > us more info, and maybe even some sample data and sample output, or at
least
> > an explanation of what you're doing with that value if you want the rest
of
> > the solution.
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "gstoa" <gstoa@.discussions.microsoft.com> wrote in message
> > news:FC26E396-3FFF-408E-B326-F5DA12FACE3B@.microsoft.com...
> > > The following SQL statement returns 38 records:
> > >
> > > SELECT Base_Contacts.ID_Contact AS CONTACTS
> > > FROM Base_Contacts LEFT OUTER JOIN
> > > Base_ContactAttributes ON Base_Contacts.ID_Contact => > > Base_ContactAttributes.ID_Contact
> > > WHERE (Base_ContactAttributes.ID_Level_Value IN (10150, 10153))
> > > GROUP BY Base_Contacts.ID_Contact
> > > HAVING (COUNT(DISTINCT Base_ContactAttributes.ID_Level_Value) = 2)
> > >
> > > Rather than retrieving the recordset, I want to simply get a count.
My
> > > syntax must be wrong (same syntax as above but I added COUNT) because
the
> > > following query simply returns 38 records with the value "1":
> > >
> > > SELECT COUNT(DISTINCT Base_Contacts.ID_Contact) AS CONTACTS
> > > FROM Base_Contacts LEFT OUTER JOIN
> > > Base_ContactAttributes ON Base_Contacts.ID_Contact => > > Base_ContactAttributes.ID_Contact
> > > WHERE (Base_ContactAttributes.ID_Level_Value IN (10150, 10153))
> > > GROUP BY Base_Contacts.ID_Contact
> > > HAVING (COUNT(DISTINCT Base_ContactAttributes.ID_Level_Value) = 2)
> > >
> > > Any suggestions for this beginner? Thanks!
> >
> >
> >|||gstoa,
you are looking for relational division. You may want to look up this
topic on the internet. (or you can just use Kalen's suggested query).
What your current query does is first calculate how many people have
attribute 1002 *or* attribute 1004. At the same time it will calculate
the number of different attributes that could be found and matched
either 1002 or 1004.
The first calculation results in 3, the second in 2. Up to this point,
the resultset is one row with these two values.
Then, all rows that do not have an attribute-count of 2 are removed. In
this case no rows are removed.
Finally, the requested columns of the resultset are returned. This is
the first calculated value of 3.
Gert-Jan
gstoa wrote:
> Here's some sample data:
<snip>
> SELECT COUNT(DISTINCT dbo.mypeople.name) AS Expr1
> FROM dbo.mypeople INNER JOIN
> dbo.myattributes ON dbo.mypeople.id_person => dbo.myattributes.id_person
> WHERE (dbo.myattributes.id_attribute IN (1002, 1004))
> HAVING (COUNT(DISTINCT dbo.myattributes.id_attribute) = 2)
<snip>
--
(Please reply only to the newsgroup)

Count function in Query

Hello SQL Experts,
I have a Query which is giving me correct result. but i was interested
in getting just the count of the Issues it returns.
select distinct
T1.dbid,T1.id,T1.title,T3.name,T1.implemented_stat us,T69.name,T1.submit_date,T1.abc_rank
from ( ( ( ( ( issue T1
INNER JOIN statedef T3 ON T1.state = T3.id )
INNER JOIN project T2 ON T1.project = T2.dbid )
LEFT OUTER JOIN parent_child_links T48mm ON T1.dbid =
T48mm.parent_dbid
and 16780481 = T48mm.parent_fielddef_id )
LEFT OUTER JOIN testrecord T48 ON T48mm.child_dbid = T48.dbid )
LEFT OUTER JOIN sw_label T69 ON T48.available_in_version = T69.dbid )
where T1.dbid <> 0
and ((T1.issue_type = 'Defect'
and T2.name = 'SW Application Platform Wilma'
and (((T1.implemented_status <> 'Not started' or
T1.implemented_status is NULL)
and T3.name in ('Assigned'))
or (T69.name = '''NULL''' and T3.name in ('Verified'))
or T3.name in ('Integrated','Postponed'))
and T1.submit_date > {ts '2006-03-25 14:59:59'}))
order by T1.id ASC
If i run the query it gives me 2930 rows but if i change the query to
return only rows using Count() function then i get wrong results some
2924 Rows
select count(distinct T1.dbid)
--T1.dbid,T1.id,T1.title,T3.name,T1.implemented_stat us,T69.name,T1.submit_date
--,T1.abc_rank
from ( ( ( ( ( issue T1
INNER JOIN statedef T3 ON T1.state = T3.id )
INNER JOIN project T2 ON T1.project = T2.dbid )
LEFT OUTER JOIN parent_child_links T48mm ON T1.dbid =
T48mm.parent_dbid
and 16780481 = T48mm.parent_fielddef_id )
LEFT OUTER JOIN testrecord T48 ON T48mm.child_dbid = T48.dbid )
LEFT OUTER JOIN sw_label T69 ON T48.available_in_version = T69.dbid )
where T1.dbid <> 0
and ((T1.issue_type = 'Defect'
and T2.name = 'SW Application Platform Wilma'
and (((T1.implemented_status <> 'Not started' or
T1.implemented_status is NULL)
and T3.name in ('Assigned'))
or (T69.name = '''NULL''' and T3.name in ('Verified'))
or T3.name in ('Integrated','Postponed'))
and T1.submit_date > {ts '2006-03-25 14:59:59'}))
order by T1.id ASC
any ways to improve this as i need to use to capture the value in a
varibale and store in some other table.
/Soni
When you do count(T1.dbid), any rows with NULL values for T1.dbid are NOT
included.
Based upon your numbers, it appears that 6 rows meeting the criteria have
NULL values for T1.dbid.
If you want the total number of rows, then count(1) or count(*)
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
<chandresh.x.soni@.sonyericsson.com> wrote in message
news:1164868492.659827.20610@.14g2000cws.googlegrou ps.com...
> Hello SQL Experts,
> I have a Query which is giving me correct result. but i was interested
> in getting just the count of the Issues it returns.
> --
> select distinct
> T1.dbid,T1.id,T1.title,T3.name,T1.implemented_stat us,T69.name,T1.submit_date,T1.abc_rank
> from ( ( ( ( ( issue T1
> INNER JOIN statedef T3 ON T1.state = T3.id )
> INNER JOIN project T2 ON T1.project = T2.dbid )
> LEFT OUTER JOIN parent_child_links T48mm ON T1.dbid =
> T48mm.parent_dbid
> and 16780481 = T48mm.parent_fielddef_id )
> LEFT OUTER JOIN testrecord T48 ON T48mm.child_dbid = T48.dbid )
> LEFT OUTER JOIN sw_label T69 ON T48.available_in_version = T69.dbid )
> where T1.dbid <> 0
> and ((T1.issue_type = 'Defect'
> and T2.name = 'SW Application Platform Wilma'
> and (((T1.implemented_status <> 'Not started' or
> T1.implemented_status is NULL)
> and T3.name in ('Assigned'))
> or (T69.name = '''NULL''' and T3.name in ('Verified'))
> or T3.name in ('Integrated','Postponed'))
> and T1.submit_date > {ts '2006-03-25 14:59:59'}))
> order by T1.id ASC
> --
> If i run the query it gives me 2930 rows but if i change the query to
> return only rows using Count() function then i get wrong results some
> 2924 Rows
> --
> select count(distinct T1.dbid)
> --T1.dbid,T1.id,T1.title,T3.name,T1.implemented_stat us,T69.name,T1.submit_date
> --,T1.abc_rank
> from ( ( ( ( ( issue T1
> INNER JOIN statedef T3 ON T1.state = T3.id )
> INNER JOIN project T2 ON T1.project = T2.dbid )
> LEFT OUTER JOIN parent_child_links T48mm ON T1.dbid =
> T48mm.parent_dbid
> and 16780481 = T48mm.parent_fielddef_id )
> LEFT OUTER JOIN testrecord T48 ON T48mm.child_dbid = T48.dbid )
> LEFT OUTER JOIN sw_label T69 ON T48.available_in_version = T69.dbid )
> where T1.dbid <> 0
> and ((T1.issue_type = 'Defect'
> and T2.name = 'SW Application Platform Wilma'
> and (((T1.implemented_status <> 'Not started' or
> T1.implemented_status is NULL)
> and T3.name in ('Assigned'))
> or (T69.name = '''NULL''' and T3.name in ('Verified'))
> or T3.name in ('Integrated','Postponed'))
> and T1.submit_date > {ts '2006-03-25 14:59:59'}))
> order by T1.id ASC
> --
> any ways to improve this as i need to use to capture the value in a
> varibale and store in some other table.
> /Soni
>
|||Hi Arnie,
First of all thanks for replying to my mail. but yr solution did not
work when i use count(*) or count(1) it returns 5282 as count
/soni
Arnie Rowland wrote:[vbcol=seagreen]
> When you do count(T1.dbid), any rows with NULL values for T1.dbid are NOT
> included.
> Based upon your numbers, it appears that 6 rows meeting the criteria have
> NULL values for T1.dbid.
> If you want the total number of rows, then count(1) or count(*)
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to the
> top yourself.
> - H. Norman Schwarzkopf
>
> <chandresh.x.soni@.sonyericsson.com> wrote in message
> news:1164868492.659827.20610@.14g2000cws.googlegrou ps.com...
|||The DISTINCT on the first query results in distinct ROWS. The
count(distinct T1.dbid) counts distinct values of T1.dbid. If you
inspect the data 2930 rows returned by the first query you will find
duplicates of T1.dbid, even with the DISTINCT.
The only way I know to count what you want counted is to place the
entire first query into a derived table, and count that.
SELECT count(*)
FROM (<insert first query here, minus ORDER BY>) as X
Roy Harvey
Beacon Falls, CT
On 29 Nov 2006 22:34:52 -0800, chandresh.x.soni@.sonyericsson.com
wrote:

>Hello SQL Experts,
>I have a Query which is giving me correct result. but i was interested
>in getting just the count of the Issues it returns.
>--
>select distinct
>T1.dbid,T1.id,T1.title,T3.name,T1.implemented_sta tus,T69.name,T1.submit_date,T1.abc_rank
>from ( ( ( ( ( issue T1
>INNER JOIN statedef T3 ON T1.state = T3.id )
>INNER JOIN project T2 ON T1.project = T2.dbid )
>LEFT OUTER JOIN parent_child_links T48mm ON T1.dbid =
>T48mm.parent_dbid
>and 16780481 = T48mm.parent_fielddef_id )
>LEFT OUTER JOIN testrecord T48 ON T48mm.child_dbid = T48.dbid )
>LEFT OUTER JOIN sw_label T69 ON T48.available_in_version = T69.dbid )
>where T1.dbid <> 0
>and ((T1.issue_type = 'Defect'
>and T2.name = 'SW Application Platform Wilma'
>and (((T1.implemented_status <> 'Not started' or
>T1.implemented_status is NULL)
>and T3.name in ('Assigned'))
>or (T69.name = '''NULL''' and T3.name in ('Verified'))
>or T3.name in ('Integrated','Postponed'))
>and T1.submit_date > {ts '2006-03-25 14:59:59'}))
>order by T1.id ASC
>--
>If i run the query it gives me 2930 rows but if i change the query to
>return only rows using Count() function then i get wrong results some
>2924 Rows
>--
>select count(distinct T1.dbid)
>--T1.dbid,T1.id,T1.title,T3.name,T1.implemented_stat us,T69.name,T1.submit_date
>--,T1.abc_rank
>from ( ( ( ( ( issue T1
>INNER JOIN statedef T3 ON T1.state = T3.id )
>INNER JOIN project T2 ON T1.project = T2.dbid )
>LEFT OUTER JOIN parent_child_links T48mm ON T1.dbid =
>T48mm.parent_dbid
>and 16780481 = T48mm.parent_fielddef_id )
>LEFT OUTER JOIN testrecord T48 ON T48mm.child_dbid = T48.dbid )
>LEFT OUTER JOIN sw_label T69 ON T48.available_in_version = T69.dbid )
>where T1.dbid <> 0
>and ((T1.issue_type = 'Defect'
>and T2.name = 'SW Application Platform Wilma'
>and (((T1.implemented_status <> 'Not started' or
>T1.implemented_status is NULL)
>and T3.name in ('Assigned'))
>or (T69.name = '''NULL''' and T3.name in ('Verified'))
>or T3.name in ('Integrated','Postponed'))
>and T1.submit_date > {ts '2006-03-25 14:59:59'}))
>order by T1.id ASC
>--
>any ways to improve this as i need to use to capture the value in a
>varibale and store in some other table.
>/Soni
|||The first query specifies DISTINCT so duplicate *rows* are omitted and the
second only counts distinct non-null T1.dbid values. I think the easiest
way to get the desired count is to wrap the original query in a derived
table. Untested example:
SELECT COUNT(*)
FROM (
select distinct
T1.dbid,T1.id,T1.title,T3.name,T1.implemented_stat us,T69.name,T1.submit_date,T1.abc_rank
from ( ( ( ( ( issue T1
INNER JOIN statedef T3 ON T1.state = T3.id )
INNER JOIN project T2 ON T1.project = T2.dbid )
LEFT OUTER JOIN parent_child_links T48mm ON T1.dbid =
T48mm.parent_dbid
and 16780481 = T48mm.parent_fielddef_id )
LEFT OUTER JOIN testrecord T48 ON T48mm.child_dbid = T48.dbid )
LEFT OUTER JOIN sw_label T69 ON T48.available_in_version = T69.dbid )
where T1.dbid <> 0
and ((T1.issue_type = 'Defect'
and T2.name = 'SW Application Platform Wilma'
and (((T1.implemented_status <> 'Not started' or
T1.implemented_status is NULL)
and T3.name in ('Assigned'))
or (T69.name = '''NULL''' and T3.name in ('Verified'))
or T3.name in ('Integrated','Postponed'))
and T1.submit_date > {ts '2006-03-25 14:59:59'}))
) AS results
Hope this helps.
Dan Guzman
SQL Server MVP
<chandresh.x.soni@.sonyericsson.com> wrote in message
news:1164868492.659827.20610@.14g2000cws.googlegrou ps.com...
> Hello SQL Experts,
> I have a Query which is giving me correct result. but i was interested
> in getting just the count of the Issues it returns.
> --
> select distinct
> T1.dbid,T1.id,T1.title,T3.name,T1.implemented_stat us,T69.name,T1.submit_date,T1.abc_rank
> from ( ( ( ( ( issue T1
> INNER JOIN statedef T3 ON T1.state = T3.id )
> INNER JOIN project T2 ON T1.project = T2.dbid )
> LEFT OUTER JOIN parent_child_links T48mm ON T1.dbid =
> T48mm.parent_dbid
> and 16780481 = T48mm.parent_fielddef_id )
> LEFT OUTER JOIN testrecord T48 ON T48mm.child_dbid = T48.dbid )
> LEFT OUTER JOIN sw_label T69 ON T48.available_in_version = T69.dbid )
> where T1.dbid <> 0
> and ((T1.issue_type = 'Defect'
> and T2.name = 'SW Application Platform Wilma'
> and (((T1.implemented_status <> 'Not started' or
> T1.implemented_status is NULL)
> and T3.name in ('Assigned'))
> or (T69.name = '''NULL''' and T3.name in ('Verified'))
> or T3.name in ('Integrated','Postponed'))
> and T1.submit_date > {ts '2006-03-25 14:59:59'}))
> order by T1.id ASC
> --
> If i run the query it gives me 2930 rows but if i change the query to
> return only rows using Count() function then i get wrong results some
> 2924 Rows
> --
> select count(distinct T1.dbid)
> --T1.dbid,T1.id,T1.title,T3.name,T1.implemented_stat us,T69.name,T1.submit_date
> --,T1.abc_rank
> from ( ( ( ( ( issue T1
> INNER JOIN statedef T3 ON T1.state = T3.id )
> INNER JOIN project T2 ON T1.project = T2.dbid )
> LEFT OUTER JOIN parent_child_links T48mm ON T1.dbid =
> T48mm.parent_dbid
> and 16780481 = T48mm.parent_fielddef_id )
> LEFT OUTER JOIN testrecord T48 ON T48mm.child_dbid = T48.dbid )
> LEFT OUTER JOIN sw_label T69 ON T48.available_in_version = T69.dbid )
> where T1.dbid <> 0
> and ((T1.issue_type = 'Defect'
> and T2.name = 'SW Application Platform Wilma'
> and (((T1.implemented_status <> 'Not started' or
> T1.implemented_status is NULL)
> and T3.name in ('Assigned'))
> or (T69.name = '''NULL''' and T3.name in ('Verified'))
> or T3.name in ('Integrated','Postponed'))
> and T1.submit_date > {ts '2006-03-25 14:59:59'}))
> order by T1.id ASC
> --
> any ways to improve this as i need to use to capture the value in a
> varibale and store in some other table.
> /Soni
>

Count function in Query

Hello SQL Experts,
I have a Query which is giving me correct result. but i was interested
in getting just the count of the Issues it returns.
select distinct
T1.dbid,T1.id,T1.title,T3.name,T1.implemented_status,T69.name,T1.submit_date
,T1.abc_rank
from ( ( ( ( ( issue T1
INNER JOIN statedef T3 ON T1.state = T3.id )
INNER JOIN project T2 ON T1.project = T2.dbid )
LEFT OUTER JOIN parent_child_links T48mm ON T1.dbid =
T48mm.parent_dbid
and 16780481 = T48mm.parent_fielddef_id )
LEFT OUTER JOIN testrecord T48 ON T48mm.child_dbid = T48.dbid )
LEFT OUTER JOIN sw_label T69 ON T48.available_in_version = T69.dbid )
where T1.dbid <> 0
and ((T1.issue_type = 'Defect'
and T2.name = 'SW Application Platform Wilma'
and (((T1.implemented_status <> 'Not started' or
T1.implemented_status is NULL)
and T3.name in ('Assigned'))
or (T69.name = '''NULL''' and T3.name in ('Verified'))
or T3.name in ('Integrated','Postponed'))
and T1.submit_date > {ts '2006-03-25 14:59:59'}))
order by T1.id ASC
--
If i run the query it gives me 2930 rows but if i change the query to
return only rows using Count() function then i get wrong results some
2924 Rows
select count(distinct T1.dbid)
--T1.dbid,T1.id,T1.title,T3.name,T1.implemented_status,T69.name,T1.submit_da
te
--,T1.abc_rank
from ( ( ( ( ( issue T1
INNER JOIN statedef T3 ON T1.state = T3.id )
INNER JOIN project T2 ON T1.project = T2.dbid )
LEFT OUTER JOIN parent_child_links T48mm ON T1.dbid =
T48mm.parent_dbid
and 16780481 = T48mm.parent_fielddef_id )
LEFT OUTER JOIN testrecord T48 ON T48mm.child_dbid = T48.dbid )
LEFT OUTER JOIN sw_label T69 ON T48.available_in_version = T69.dbid )
where T1.dbid <> 0
and ((T1.issue_type = 'Defect'
and T2.name = 'SW Application Platform Wilma'
and (((T1.implemented_status <> 'Not started' or
T1.implemented_status is NULL)
and T3.name in ('Assigned'))
or (T69.name = '''NULL''' and T3.name in ('Verified'))
or T3.name in ('Integrated','Postponed'))
and T1.submit_date > {ts '2006-03-25 14:59:59'}))
order by T1.id ASC
--
any ways to improve this as i need to use to capture the value in a
varibale and store in some other table.
/SoniWhen you do count(T1.dbid), any rows with NULL values for T1.dbid are NOT
included.
Based upon your numbers, it appears that 6 rows meeting the criteria have
NULL values for T1.dbid.
If you want the total number of rows, then count(1) or count(*)
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
<chandresh.x.soni@.sonyericsson.com> wrote in message
news:1164868492.659827.20610@.14g2000cws.googlegroups.com...
> Hello SQL Experts,
> I have a Query which is giving me correct result. but i was interested
> in getting just the count of the Issues it returns.
> --
> select distinct
> T1.dbid,T1.id,T1.title,T3.name,T1.implemented_status,T69.name,T1.submit_da
te,T1.abc_rank
> from ( ( ( ( ( issue T1
> INNER JOIN statedef T3 ON T1.state = T3.id )
> INNER JOIN project T2 ON T1.project = T2.dbid )
> LEFT OUTER JOIN parent_child_links T48mm ON T1.dbid =
> T48mm.parent_dbid
> and 16780481 = T48mm.parent_fielddef_id )
> LEFT OUTER JOIN testrecord T48 ON T48mm.child_dbid = T48.dbid )
> LEFT OUTER JOIN sw_label T69 ON T48.available_in_version = T69.dbid )
> where T1.dbid <> 0
> and ((T1.issue_type = 'Defect'
> and T2.name = 'SW Application Platform Wilma'
> and (((T1.implemented_status <> 'Not started' or
> T1.implemented_status is NULL)
> and T3.name in ('Assigned'))
> or (T69.name = '''NULL''' and T3.name in ('Verified'))
> or T3.name in ('Integrated','Postponed'))
> and T1.submit_date > {ts '2006-03-25 14:59:59'}))
> order by T1.id ASC
> --
> If i run the query it gives me 2930 rows but if i change the query to
> return only rows using Count() function then i get wrong results some
> 2924 Rows
> --
> select count(distinct T1.dbid)
> --T1.dbid,T1.id,T1.title,T3.name,T1.implemented_status,T69.name,T1.submit_
date
> --,T1.abc_rank
> from ( ( ( ( ( issue T1
> INNER JOIN statedef T3 ON T1.state = T3.id )
> INNER JOIN project T2 ON T1.project = T2.dbid )
> LEFT OUTER JOIN parent_child_links T48mm ON T1.dbid =
> T48mm.parent_dbid
> and 16780481 = T48mm.parent_fielddef_id )
> LEFT OUTER JOIN testrecord T48 ON T48mm.child_dbid = T48.dbid )
> LEFT OUTER JOIN sw_label T69 ON T48.available_in_version = T69.dbid )
> where T1.dbid <> 0
> and ((T1.issue_type = 'Defect'
> and T2.name = 'SW Application Platform Wilma'
> and (((T1.implemented_status <> 'Not started' or
> T1.implemented_status is NULL)
> and T3.name in ('Assigned'))
> or (T69.name = '''NULL''' and T3.name in ('Verified'))
> or T3.name in ('Integrated','Postponed'))
> and T1.submit_date > {ts '2006-03-25 14:59:59'}))
> order by T1.id ASC
> --
> any ways to improve this as i need to use to capture the value in a
> varibale and store in some other table.
> /Soni
>|||Hi Arnie,
First of all thanks for replying to my mail. but yr solution did not
work when i use count(*) or count(1) it returns 5282 as count
/soni
Arnie Rowland wrote:[vbcol=seagreen]
> When you do count(T1.dbid), any rows with NULL values for T1.dbid are NOT
> included.
> Based upon your numbers, it appears that 6 rows meeting the criteria have
> NULL values for T1.dbid.
> If you want the total number of rows, then count(1) or count(*)
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to th
e
> top yourself.
> - H. Norman Schwarzkopf
>
> <chandresh.x.soni@.sonyericsson.com> wrote in message
> news:1164868492.659827.20610@.14g2000cws.googlegroups.com...|||what if you select count(distinct col) from table...
<chandresh.x.soni@.sonyericsson.com> wrote in message
news:1164870965.270003.52360@.80g2000cwy.googlegroups.com...
> Hi Arnie,
> First of all thanks for replying to my mail. but yr solution did not
> work when i use count(*) or count(1) it returns 5282 as count
> /soni
> Arnie Rowland wrote:
>|||The DISTINCT on the first query results in distinct ROWS. The
count(distinct T1.dbid) counts distinct values of T1.dbid. If you
inspect the data 2930 rows returned by the first query you will find
duplicates of T1.dbid, even with the DISTINCT.
The only way I know to count what you want counted is to place the
entire first query into a derived table, and count that.
SELECT count(*)
FROM (<insert first query here, minus ORDER BY> ) as X
Roy Harvey
Beacon Falls, CT
On 29 Nov 2006 22:34:52 -0800, chandresh.x.soni@.sonyericsson.com
wrote:

>Hello SQL Experts,
>I have a Query which is giving me correct result. but i was interested
>in getting just the count of the Issues it returns.
>--
>select distinct
>T1.dbid,T1.id,T1.title,T3.name,T1.implemented_status,T69.name,T1.submit_dat
e,T1.abc_rank
>from ( ( ( ( ( issue T1
> INNER JOIN statedef T3 ON T1.state = T3.id )
> INNER JOIN project T2 ON T1.project = T2.dbid )
> LEFT OUTER JOIN parent_child_links T48mm ON T1.dbid =
>T48mm.parent_dbid
> and 16780481 = T48mm.parent_fielddef_id )
> LEFT OUTER JOIN testrecord T48 ON T48mm.child_dbid = T48.dbid )
> LEFT OUTER JOIN sw_label T69 ON T48.available_in_version = T69.dbid )
>where T1.dbid <> 0
> and ((T1.issue_type = 'Defect'
> and T2.name = 'SW Application Platform Wilma'
> and (((T1.implemented_status <> 'Not started' or
>T1.implemented_status is NULL)
> and T3.name in ('Assigned'))
> or (T69.name = '''NULL''' and T3.name in ('Verified'))
> or T3.name in ('Integrated','Postponed'))
> and T1.submit_date > {ts '2006-03-25 14:59:59'}))
>order by T1.id ASC
>--
>If i run the query it gives me 2930 rows but if i change the query to
>return only rows using Count() function then i get wrong results some
>2924 Rows
>--
>select count(distinct T1.dbid)
>--T1.dbid,T1.id,T1.title,T3.name,T1.implemented_status,T69.name,T1.submit_d
ate
>--,T1.abc_rank
>from ( ( ( ( ( issue T1
> INNER JOIN statedef T3 ON T1.state = T3.id )
> INNER JOIN project T2 ON T1.project = T2.dbid )
> LEFT OUTER JOIN parent_child_links T48mm ON T1.dbid =
>T48mm.parent_dbid
> and 16780481 = T48mm.parent_fielddef_id )
> LEFT OUTER JOIN testrecord T48 ON T48mm.child_dbid = T48.dbid )
> LEFT OUTER JOIN sw_label T69 ON T48.available_in_version = T69.dbid )
>where T1.dbid <> 0
> and ((T1.issue_type = 'Defect'
> and T2.name = 'SW Application Platform Wilma'
> and (((T1.implemented_status <> 'Not started' or
>T1.implemented_status is NULL)
> and T3.name in ('Assigned'))
> or (T69.name = '''NULL''' and T3.name in ('Verified'))
> or T3.name in ('Integrated','Postponed'))
> and T1.submit_date > {ts '2006-03-25 14:59:59'}))
>order by T1.id ASC
>--
>any ways to improve this as i need to use to capture the value in a
>varibale and store in some other table.
>/Soni|||The first query specifies DISTINCT so duplicate *rows* are omitted and the
second only counts distinct non-null T1.dbid values. I think the easiest
way to get the desired count is to wrap the original query in a derived
table. Untested example:
SELECT COUNT(*)
FROM (
select distinct
T1.dbid,T1.id,T1.title,T3.name,T1.implemented_status,T69.name,T1.submit_date
,T1.abc_rank
from ( ( ( ( ( issue T1
INNER JOIN statedef T3 ON T1.state = T3.id )
INNER JOIN project T2 ON T1.project = T2.dbid )
LEFT OUTER JOIN parent_child_links T48mm ON T1.dbid =
T48mm.parent_dbid
and 16780481 = T48mm.parent_fielddef_id )
LEFT OUTER JOIN testrecord T48 ON T48mm.child_dbid = T48.dbid )
LEFT OUTER JOIN sw_label T69 ON T48.available_in_version = T69.dbid )
where T1.dbid <> 0
and ((T1.issue_type = 'Defect'
and T2.name = 'SW Application Platform Wilma'
and (((T1.implemented_status <> 'Not started' or
T1.implemented_status is NULL)
and T3.name in ('Assigned'))
or (T69.name = '''NULL''' and T3.name in ('Verified'))
or T3.name in ('Integrated','Postponed'))
and T1.submit_date > {ts '2006-03-25 14:59:59'}))
) AS results
Hope this helps.
Dan Guzman
SQL Server MVP
<chandresh.x.soni@.sonyericsson.com> wrote in message
news:1164868492.659827.20610@.14g2000cws.googlegroups.com...
> Hello SQL Experts,
> I have a Query which is giving me correct result. but i was interested
> in getting just the count of the Issues it returns.
> --
> select distinct
> T1.dbid,T1.id,T1.title,T3.name,T1.implemented_status,T69.name,T1.submit_da
te,T1.abc_rank
> from ( ( ( ( ( issue T1
> INNER JOIN statedef T3 ON T1.state = T3.id )
> INNER JOIN project T2 ON T1.project = T2.dbid )
> LEFT OUTER JOIN parent_child_links T48mm ON T1.dbid =
> T48mm.parent_dbid
> and 16780481 = T48mm.parent_fielddef_id )
> LEFT OUTER JOIN testrecord T48 ON T48mm.child_dbid = T48.dbid )
> LEFT OUTER JOIN sw_label T69 ON T48.available_in_version = T69.dbid )
> where T1.dbid <> 0
> and ((T1.issue_type = 'Defect'
> and T2.name = 'SW Application Platform Wilma'
> and (((T1.implemented_status <> 'Not started' or
> T1.implemented_status is NULL)
> and T3.name in ('Assigned'))
> or (T69.name = '''NULL''' and T3.name in ('Verified'))
> or T3.name in ('Integrated','Postponed'))
> and T1.submit_date > {ts '2006-03-25 14:59:59'}))
> order by T1.id ASC
> --
> If i run the query it gives me 2930 rows but if i change the query to
> return only rows using Count() function then i get wrong results some
> 2924 Rows
> --
> select count(distinct T1.dbid)
> --T1.dbid,T1.id,T1.title,T3.name,T1.implemented_status,T69.name,T1.submit_
date
> --,T1.abc_rank
> from ( ( ( ( ( issue T1
> INNER JOIN statedef T3 ON T1.state = T3.id )
> INNER JOIN project T2 ON T1.project = T2.dbid )
> LEFT OUTER JOIN parent_child_links T48mm ON T1.dbid =
> T48mm.parent_dbid
> and 16780481 = T48mm.parent_fielddef_id )
> LEFT OUTER JOIN testrecord T48 ON T48mm.child_dbid = T48.dbid )
> LEFT OUTER JOIN sw_label T69 ON T48.available_in_version = T69.dbid )
> where T1.dbid <> 0
> and ((T1.issue_type = 'Defect'
> and T2.name = 'SW Application Platform Wilma'
> and (((T1.implemented_status <> 'Not started' or
> T1.implemented_status is NULL)
> and T3.name in ('Assigned'))
> or (T69.name = '''NULL''' and T3.name in ('Verified'))
> or T3.name in ('Integrated','Postponed'))
> and T1.submit_date > {ts '2006-03-25 14:59:59'}))
> order by T1.id ASC
> --
> any ways to improve this as i need to use to capture the value in a
> varibale and store in some other table.
> /Soni
>

count from distinct

Hello

for mS SQL 2000

with

SELECT DISTINCT Name, Region
FROM Groups
GROUP BY Name, Region

I get 254 rows

but how can I get the COUNT only ?

something like

SELECT COUNT(SELECT DISTINCT Name, Region
FROM Groups
GROUP BY Name, Region) AS CPT FROM Groups

i must get 254

thank you for helpingsChange query according to your requirement...

USE Pubs

select count (*) as row_count from (select type, pub_id from titles group by type, pub_id) t

select type, pub_id from titles group by type, pub_id|||it works perfetly

thanks a lot Rajesh Patel|||Que?

SELECT COUNT(DISTINCT Name, Region)
FROM Groups

Doesn't work?

EDIT: No it doesn't...but

USE Northwind
GO
SELECT COUNT(DISTINCT CustomerID+CONVERT(varchar(10),EmployeeID)) FROM Orders

Does|||i'm liking rajesh's solution way more, brett ;)sql

Count Frequencies of row using sql query

I'm new to t-sql, but i would like to know how to generate a frequency of a row, let say i would like to have a query to select the number of times the result of 'Good' shows up, and number of times the result of 'Perfect' shows up in the data provided. i've got like thousands of rows with hundreds of different result groups, in the data sample below i just use 3(Good, Perfect, and Bad) of them. can anyone give me a suggestion on how to do this? thanks.

CREATETABLE #Count(Result VARCHAR(60))

INSERTINTO #Count(Result)VALUES('Good')

INSERTINTO #Count(Result)VALUES('Bad')

INSERTINTO #Count(Result)VALUES('Perfect')

INSERTINTO #Count(Result)VALUES('Good')

INSERTINTO #Count(Result)VALUES('Perfect')

INSERTINTO #Count(Result)VALUES('Bad')

INSERTINTO #Count(Result)VALUES('Perfect')

INSERTINTO #Count(Result)VALUES('Good')

SELECTCOUNT(Result)[ResultBad]FROM #Count where result ='Bad'

groupby result

DROPTABLE #Count

ResultBad

Check for "group by" clause in BOL.

select Result, Count(*) as cnt

from #Count

group by Result

order by Result

AMB

|||

Code Snippet

select ResultBad= sum(case Result when 'Bad' then 1 else 0 end) ,
ResultGood= sum(case Result when 'Good' then 1 else 0 end) ,
ResultPerfect= sum(case Result when 'Perfect' then 1 else 0 end)
from #count

You can also use PIVOT if you are using Sql Server 2005...

http://msdn2.microsoft.com/en-us/library/ms177410(SQL.90).aspx

|||

You have already discovered or learned how to find dupes.

Are you asking how to calculate a probability?

Such as:

Code Snippet

CREATETABLE #Count(Result VARCHAR(60))

INSERTINTO #Count(Result)VALUES('Good')

INSERTINTO #Count(Result)VALUES('Bad')

INSERTINTO #Count(Result)VALUES('Perfect')

INSERTINTO #Count(Result)VALUES('Good')

INSERTINTO #Count(Result)VALUES('Perfect')

INSERTINTO #Count(Result)VALUES('Bad')

INSERTINTO #Count(Result)VALUES('Perfect')

INSERTINTO #Count(Result)VALUES('Good')

DECLARE @.frequency asint

DECLARE @.Count asint

DECLARE @.Probability asint

SET @.Frequency =(SELECTCOUNT(Result)[ResultBad] FROM #Count where result ='Perfect'

groupby result)

SET @.Count =(SELECTCOUNT(*)FROM #Count)

SET @.Probability = @.Frequency * 100 / @.Count

SELECTCAST(@.Probability as varchar(5))+'% Chance'

DROPTABLE #Count

Adamus

|||Thanks y'all, i should have known that i only need to modify my select statement. Sorry for confusion, but thanks!!

Count for each word in a field

Is it possible to create a SELECT query that will give me the count of all the words in a single field?

SELECT field1, count(field1) FROM table1 GROUP BY field1

won't do it because field1 has multiple words and I want them broken out and counted individually.

Data example:

Field1
-------
dog
dog ate my homework
cat
dog and cat
tail
dog tail
...

I want to get a count of every occurance of each word (e.g. "dog") whether in the field by itself or with other words.

Hope this makes sense.

AlNo, you would require a function that takes a text string as input, and counts the number of occurences of another string in it so that you could write:

select field1, word_count(field1,'dog')
from table1;

Depending on your DBMS's capabilities, such a function may already exist or you may be able to write one for yourself. In Oracle for example, you could certainly write one yourself and maybe one already exists in the Oracle Text tool (I don't know).|||Tony,

Thanks for the response. I forgot to mention the DB engine I'm using: MySQL.

I'm not sure I explained it well. Per your function example you're passing the word "dog" to the function. I need it to count all of the words in each field. Using my original example, the results would look like this (with an ORDER BY added):

field1 qty
-- --
dog 4
cat 3
tail 2
and 1
ate 1
my 1

It probably still requires a function to accomplish this.

Al|||Oh, I see - that's rather different. What you need is first to split out all the words into one per row like this:

word
--
dog
dog
dog
dog
cat
cat
cat
tail
tail
and
ate
my

Then of course it is easy to group and count the words. But how to split them up? If you could be sure there were no more than N words in any sentence then you could use a brute-force approach with a user-defined function like this:

select get_word(field1,1) as word from table1
union all
select get_word(field1,2) as word from table1
union all
...
union all
select get_word(field1,N) as word from table1

However, that probably isn't what you need. I don't know MySQL at all, but in Oracle you could achieve this (without the "N" words limit) by writing a function that returns a collection (like an array), and then selecting from the results of the function - a fairly complex operation.

It may be that this can't be done in MySQL using just a select statement - you may have to write a program that populates a temporary table with all the words, and then select from that.|||I'm thinking I'll create a new table that will house the individual words and after writing the "phrase" to field1, I'll parse the words and write them to the new table for future counting.

Thanks for your feedback!
Al

Count Distinct

Hello,

I was wondering does count(distinct) work on multiiple columns? I get syntax error on "," with the following code.

select count(distinct cookie1, cookie2) from BookersLookers_DataSet

Thanks,

-Lawrence

You cant pass more than one column in COUNT function..

Use the following query...

Code Snippet

Select Count(*) From

(Select cookie1,cookie2 From BookersLookers_DataSet Group By cookie1,cookie2) as Data

--Or

Select Count(*) From

(Select Distinct cookie1,cookie2 From BookersLookers_DataSet) as Data

|||

Actually, to get the count(), you need to use a GROUP BY, and if you use a GROUP BY, then DISTINCT is not necessary (redundent).

Code Snippet

SELECT
Cookie1,
Cookie2,
[Count] = count(1)
FROM BookersLookers_Dataset
GROUP BY
Cookie1,
Cookie2

Count Attempts!

Hi all,
I want to SELECT the most recent ATTEMPT_ID (in this case attempt_id=3)
and determine how many attempts (using COUNT) have IDENTICAL QUESTION LISTS
based on the ATTEMPT_RESULTS table?
IE:
Attempt 2 has an IDENTICAL QUESTION LIST (to 3) in the ATTEMPT_RESULTS
table using the DDL below.
Attempt 1 does not qaulify because it has an extra question 'id = 4'.
Based on the data below the result of this query should be 2.
I would appreciate any help in this, as i am not sure how to implement this
logic in SQL.
Thanks to those who responsd.
DDL:
CREATE TABLE [dbo].[attempts](
[attempt_id] [int] IDENTITY(1,1) NOT NULL,
[name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_attempts] PRIMARY KEY CLUSTERED
([attempt_id] ASC) ON [PRIMARY]) ON [PRIMARY]
CREATE TABLE [dbo].[attempt_results](
[attempt_result_id] [int] IDENTITY(1,1) NOT NULL,
[attempt_id] [int] NULL,
[question_id] [int] NOT NULL,
CONSTRAINT [PK_attempt_results] PRIMARY KEY CLUSTERED
([attempt_result_id] ASC) ON [PRIMARY]) ON [PRIMARY]
INSERT INTO [attempts] ([name]) VALUES ('Temp 1')
INSERT INTO [attempts] ([name]) VALUES ('Temp 2')
INSERT INTO [attempts] ([name]) VALUES ('Temp 3')
INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (1,1)
INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (1,2)
INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (1,3)
INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (1,4)
INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (2,1)
INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (2,2)
INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (2,3)
INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (3,1)
INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (3,2)
INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (3,3)Hi Adam
Thanks for the DDL and example data. Maybe something like the following is
what you require:
SELECT r.[attempt_id]
FROM [dbo].[attempt_results] r
JOIN [dbo].[attempt_results] a ON a.[attempt_id] = 3
AND a.[attempt_id] <> r.[attempt_id]
AND a.[question_id] = r.[question_id]
GROUP BY r.[attempt_id]
HAVING count(*) = ( SELECT COUNT(question_id) as cnt
FROM [dbo].[attempt_results]
WHERE [attempt_id] = 3 )
John
"Adam Knight" wrote:

> Hi all,
> I want to SELECT the most recent ATTEMPT_ID (in this case attempt_id=3)
> and determine how many attempts (using COUNT) have IDENTICAL QUESTION LIST
S
> based on the ATTEMPT_RESULTS table?
> IE:
> Attempt 2 has an IDENTICAL QUESTION LIST (to 3) in the ATTEMPT_RESULTS
> table using the DDL below.
> Attempt 1 does not qaulify because it has an extra question 'id = 4'.
> Based on the data below the result of this query should be 2.
> I would appreciate any help in this, as i am not sure how to implement thi
s
> logic in SQL.
> Thanks to those who responsd.
> DDL:
> CREATE TABLE [dbo].[attempts](
> [attempt_id] [int] IDENTITY(1,1) NOT NULL,
> [name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> CONSTRAINT [PK_attempts] PRIMARY KEY CLUSTERED
> ([attempt_id] ASC) ON [PRIMARY]) ON [PRIMARY]
>
> CREATE TABLE [dbo].[attempt_results](
> [attempt_result_id] [int] IDENTITY(1,1) NOT NULL,
> [attempt_id] [int] NULL,
> [question_id] [int] NOT NULL,
> CONSTRAINT [PK_attempt_results] PRIMARY KEY CLUSTERED
> ([attempt_result_id] ASC) ON [PRIMARY]) ON [PRIMARY]
> INSERT INTO [attempts] ([name]) VALUES ('Temp 1')
> INSERT INTO [attempts] ([name]) VALUES ('Temp 2')
> INSERT INTO [attempts] ([name]) VALUES ('Temp 3')
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (1,1)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (1,2)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (1,3)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (1,4)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (2,1)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (2,2)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (2,3)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (3,1)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (3,2)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (3,3)
>
>|||this should do:
e.g.
create function dbo.getlist(@.attempt_id int)
returns sysname
as
begin
declare @.s sysname
select @.s=isnull(@.s+'|','')+cast(question_id as sysname)
from attempt_results
where attempt_id=@.attempt_id
order by question_id
return @.s
end
go
select x.id,count(*) cnt
from(select max(attempt_id) id
from attempt_results) x join (select distinct attempt_id id
from attempt_results) y
on dbo.getlist(x.id)=dbo.getlist(y.id)
group by x.id
-oj
"Adam Knight" <adam@.pertrain.com.au> wrote in message
news:%23WIiFcYxFHA.700@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I want to SELECT the most recent ATTEMPT_ID (in this case attempt_id=3)
> and determine how many attempts (using COUNT) have IDENTICAL QUESTION
> LISTS based on the ATTEMPT_RESULTS table?
> IE:
> Attempt 2 has an IDENTICAL QUESTION LIST (to 3) in the ATTEMPT_RESULTS
> table using the DDL below.
> Attempt 1 does not qaulify because it has an extra question 'id = 4'.
> Based on the data below the result of this query should be 2.
> I would appreciate any help in this, as i am not sure how to implement
> this logic in SQL.
> Thanks to those who responsd.
> DDL:
> CREATE TABLE [dbo].[attempts](
> [attempt_id] [int] IDENTITY(1,1) NOT NULL,
> [name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> CONSTRAINT [PK_attempts] PRIMARY KEY CLUSTERED
> ([attempt_id] ASC) ON [PRIMARY]) ON [PRIMARY]
>
> CREATE TABLE [dbo].[attempt_results](
> [attempt_result_id] [int] IDENTITY(1,1) NOT NULL,
> [attempt_id] [int] NULL,
> [question_id] [int] NOT NULL,
> CONSTRAINT [PK_attempt_results] PRIMARY KEY CLUSTERED
> ([attempt_result_id] ASC) ON [PRIMARY]) ON [PRIMARY]
> INSERT INTO [attempts] ([name]) VALUES ('Temp 1')
> INSERT INTO [attempts] ([name]) VALUES ('Temp 2')
> INSERT INTO [attempts] ([name]) VALUES ('Temp 3')
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (1,1)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (1,2)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (1,3)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (1,4)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (2,1)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (2,2)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (2,3)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (3,1)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (3,2)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (3,3)
>
>sql

Thursday, March 22, 2012

COUNT and TOP

I want to do something like this:

SELECT COUNT (SELECT TOP(10) * FROM MyTable order by Date Desc) FROM MyTable where User = 'Scott'

What I want is to return the number of affected rows where the column 'User' equals 'Scott'...But is should only check in the 10 latest inserted rows....

Hope you understand what I mean...

when I want to do things like that especially if performance isnt completly critical, I just take the easier to read approach and do a sub query.

Select Count(*)
FROM ( Select Top(10) * From... ) As MyAlias

That way you know for sure things will be working out as you are thinking them.|||

Hi Tigers21,

Use the following: -

SELECT COUNT(*) FROM MyTable WHERE MyTableUniqueFieldID IN
(
SELECT TOP 10 MyTableUniqueFieldID FROM MyTable
ORDER BY [DATE] DESC
)
AND [User] = 'scott'

Substitute MyTableUniqueFieldID with the primary key field of the MyTable table.

Kind regards

Scotty

|||

Thanks for your answers!...

Both ways seems to work correct for me, but which one is best for the performance?

|||

Shados has the better solution.

Count and top

I have complicated select statement which returns a lot of rows.
I would like to show only top 50 rows on client, but also notice the client
with the number of all rows of his search statement, like
Showing top 50 of 1000 rows.
How can I get count of all rows, something like:
SELECT top 50 *, count(all) from .......
One way is to create 2 select statements:
first one to get count of all records suitable to client search
SELECT count(*) FROM ....WHERE ...
and second to show the client only top 50 of them:
SELECT top 50 * FROM...WHERE ...
I wonder, can this be done with one select statement?
Regards,SSee the following link for a number of possible options.
http://www.aspfaq.com/show.asp?id=2120
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"simonZ" <simon.zupan@.studio-moderna.com> wrote in message
news:%232lQ41obGHA.3388@.TK2MSFTNGP05.phx.gbl...
>I have complicated select statement which returns a lot of rows.
> I would like to show only top 50 rows on client, but also notice the
> client with the number of all rows of his search statement, like
> Showing top 50 of 1000 rows.
> How can I get count of all rows, something like:
> SELECT top 50 *, count(all) from .......
> One way is to create 2 select statements:
> first one to get count of all records suitable to client search
> SELECT count(*) FROM ....WHERE ...
> and second to show the client only top 50 of them:
> SELECT top 50 * FROM...WHERE ...
> I wonder, can this be done with one select statement?
> Regards,S
>
>|||You can use @.@.ROWCOUNT.
And make a sp returns ROWCOUNT.
SELECT Top 50 * FROM WHERE..,
SELECT @.@.ROWCOUNT
"simonZ"?? ??? ??:

> I have complicated select statement which returns a lot of rows.
> I would like to show only top 50 rows on client, but also notice the clien
t
> with the number of all rows of his search statement, like
> Showing top 50 of 1000 rows.
> How can I get count of all rows, something like:
> SELECT top 50 *, count(all) from .......
> One way is to create 2 select statements:
> first one to get count of all records suitable to client search
> SELECT count(*) FROM ....WHERE ...
> and second to show the client only top 50 of them:
> SELECT top 50 * FROM...WHERE ...
> I wonder, can this be done with one select statement?
> Regards,S
>
>
>|||It can be done in one select that queries the table twice. Use a
subquery in the SELECT column list.
SELECT top 50 *,
(select count(all) from .......) as CountAll
FROM ......
Roy Harvey
Beacon Falls, CT
On Wed, 3 May 2006 10:42:07 +0200, "simonZ"
<simon.zupan@.studio-moderna.com> wrote:

>I have complicated select statement which returns a lot of rows.
>I would like to show only top 50 rows on client, but also notice the client
>with the number of all rows of his search statement, like
>Showing top 50 of 1000 rows.
>How can I get count of all rows, something like:
>SELECT top 50 *, count(all) from .......
>One way is to create 2 select statements:
>first one to get count of all records suitable to client search
>SELECT count(*) FROM ....WHERE ...
>and second to show the client only top 50 of them:
>SELECT top 50 * FROM...WHERE ...
>I wonder, can this be done with one select statement?
>Regards,S
>
>|||hongju je napisal:
> You can use @.@.ROWCOUNT.
> And make a sp returns ROWCOUNT.
> SELECT Top 50 * FROM WHERE..,
> SELECT @.@.ROWCOUNT
>
> "simonZ"=EB=8B=98=EC=9D=B4 =EC=9E=91=EC=84=B1=ED=95=9C =EB=82=B4=EC=9A=A9:
>
ient

Thursday, March 29, 2012

Count Records Between two dates

count of subqueries plz..

Count of same column appearing twice in the query

Count of matching result sets

Count of matching result sets

Count of matching result sets

Tuesday, March 27, 2012

COUNT of datediff

Count of Counts in SQL Server

Count Item function

Count Item function

Sunday, March 25, 2012

COUNT function not working with GROUP BY

Count function in Query

Count function in Query

count from distinct

Count Frequencies of row using sql query

Count for each word in a field

Count Distinct

Count Attempts!

Thursday, March 22, 2012

COUNT and TOP

Count and top

Counter settings

Blog Archive

About Me