Help with SQL query please

I have a table that have 4 fields of interest, A, B, C and D (plus others
that are not of interest).
I am not interested in completely unique rows (based on the fields A,B,C &
D).
What I am interested in rows where the A, B & C fields are identical, but
within these identical "groups" the D field is different.
So, I though I'd write an SQL statement that uses a sub query.
The inner query would bring back all rows that are not unique (by using a
count > 1 statement) and the outer query would then get the details
The SQL I came up with is as follows.
The query is as follows:
---
select A, B, C, D
from X
where exists
(select A, B, C, count(D)
from X
group by A, B, C
having count(D) > 1)
order by A, B, C
---
However...when running the sub query in isolation and ordering the results,
I found that the lowest value of "A" returned was 3. But, when I run the
query as a whole, the first set of results returned have data for rows that
contain A values of 1 & 2.
Puzzled over this, but now need some help.
Thanks in advance if you can provide this...
GriffPlease ALWAYS post DDL with questions like this so that we don't have
to guess what your table looks like. It also helps to include some
sample data and show your required end result.
The problems with the query you posted seem to be twofold. First the
subquery isn't correlated. Second COUNT(D) will count >1 if there is
more than one row even if D is the same (non-null) in each case. That
would make sense if (A,B,C,D) is a key of this table but you didn't
actually specify a key (did I mention about the importance of including
DDL?).
Assuming (A,B,C,D) is not nullable (yes, the DDL would have told us
that too) you can do it with a correlated subquery:
SELECT x.a, x.b, x.c, x.d
FROM x
WHERE EXISTS
(SELECT *
FROM x AS z
WHERE x.a = z.a
AND x.b = z.b
AND x.c = x.c
AND x.d <> z.d)
ORDER BY x.a, x.b, x.c, x.d ;
or with a derived table:
SELECT x.a, x.b, x.c, x.d
FROM
(SELECT a, b, c
FROM x
GROUP BY a, b, c
HAVING MIN(d)<MAX(d)) AS z
JOIN x
ON x.a = z.a
AND x.b = z.b
AND x.c = x.c
ORDER BY x.a, x.b, x.c, x.d ;
(both untested)
Essential reading on the best way to post a problem here:
http://www.aspfaq.com/etiquette.asp?id=5006
Hope this helps.
David Portas
SQL Server MVP
--

Help with SQL query is required please

Hi

I have the following table:

date number called answered
2006-08-10 9051231234 0
2006-08-10 9051231235 1
2006-08-11 9051231231 0
2006-08-11 9051231211 0
2006-08-06 9051231222 1
2006-08-06 9051231233 0
2006-08-06 9051231233 0

need to get the report on how many calls have been placed for particular day and how many were answered, something like this:

2006-08-06 3 1
2006-08-10 2 0
2006-08-11 2 1

How to do this in one query or without creating an interim table.
Thanks.Can you post the URL or a copy of the assignment exactly as it came from the teacher? There are usually some subtle quirks in how the assignments are worded that will influcence how you need to solve the problem to get a good grade.

It would also help to know exactly what hardware/software they'll test this on, since that can influence the choices we make too.

-PatP|||pat, you cynic

905 area code is just outside toronto, and i happen to know there are no sql classes going on right now

this has to be a real world problem, not a homework assignment!! :)

xirurg, would you please show the query that you've managed to work out by yourself so far? use correct table and column names, please|||This should be relatively simple, viz. grouping by date, counting to find the second column, and summing to get the third one.|||SQL classes? Yes I think I'd need them :-). Yes it's the real issue.
Software is mySQL. Here are the fields which matter (there are more in the table but they are irrelevant for this task):
+----+-----+--+--+-------+---+
| Field | Type | Null | Key | Default | Extra |
+----+-----+--+--+-------+---+
| calldate | datetime | | MUL | 0000-00-00 00:00:00 | |
| dcontext | varchar(80) | | | | |
| disposition | varchar(45) | | | | |

dcontext has values outbond or incoming - I'm interesting in outbound only. Disposition has values ANSWERED, NO ANSWER, FAILED. I'm interesed in ANSWERED only.
Report should look like:
calldate, number of outbound calls, number of answered calls.

I can get those numbers by using 2 separate queries:
select left(calldate,11),count(*) from cdr where dcontext<>"incoming" group by left(calldate,11);
select left(calldate,11),count(*) from cdr where disposition="ANSWERED" and dcontext<>"incoming" group by left(calldate,11);

but then I have to combine results either in temp table or inside programming code (Perl in this case) so I was thinking there is a way to do it in one query. I tried to use UNION SELECT and CUBE grouping, CASE as well - no luck

Xirurg

pat, you cynic

905 area code is just outside toronto, and i happen to know there are no sql classes going on right now

this has to be a real world problem, not a homework assignment!! :)

xirurg, would you please show the query that you've managed to work out by yourself so far? use correct table and column names, please|||dcontext has values outbond or incoming - I'm interesting in outbound only. Disposition has values ANSWERED, NO ANSWER, FAILED. I'm interesed in ANSWERED only.
Try this:SELECT LEFT(calldate,11) AS calldate,
COUNT(*) AS number_of_outbound_calls,
SUM(CASE disposition WHEN 'ANSWERED' THEN 1 ELSE 0 END)
AS number_of_answered_calls
FROM cdr
WHERE dcontext<>'incoming'
GROUP BY LEFT(calldate,11)Instead of "LEFT(calldate,11)" also try "CAST(calldate AS date)" -- should be better in terms of performance, in case mySQL supports this.|||Thanks Peter

Help with SQL query from ASP

Hi,
Not sure if I have posted this is the right place, if anyone could
point me in the direction if I'm wrong I'd be grateful.
Anyway, heres my query, I have a SQL query which is searching records
where a date has past, and it works fine in SQL query analyser. Heres
an example of the query...
SELECT
PROD_NO,
B.BRAND,
PRODUCT_NAME,
D.DATE_SENT,
D.DATE_RETURN
FROM dbo.PRODUCTS AS P
INNER JOIN BRANDS AS B
ON P.BRAND_ID = B.BRAND_ID
INNER DATES AS D
ON D.UID = P.UID
WHERE STATUS_ID = '3'
AND DATE_RETURN < '2005-04-25 00:00:00'
This works fine in query analyser, but when I pass the same script to
the server from an ASP page (where I am replacing the date_return value
with an ASP variable representing todays date as above) I get the
following message...
"The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value."
Does anyone have any ideas? Maybe I'm going the wrong way about this?
Thanks
STR8Badly formatted datetime string. I suggest you start by reading relevant sec
tions here:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<straighteight@.gmail.com> wrote in message
news:1114513868.795922.276930@.g14g2000cwa.googlegroups.com...
> Hi,
> Not sure if I have posted this is the right place, if anyone could
> point me in the direction if I'm wrong I'd be grateful.
> Anyway, heres my query, I have a SQL query which is searching records
> where a date has past, and it works fine in SQL query analyser. Heres
> an example of the query...
> SELECT
> PROD_NO,
> B.BRAND,
> PRODUCT_NAME,
> D.DATE_SENT,
> D.DATE_RETURN
> FROM dbo.PRODUCTS AS P
> INNER JOIN BRANDS AS B
> ON P.BRAND_ID = B.BRAND_ID
> INNER DATES AS D
> ON D.UID = P.UID
> WHERE STATUS_ID = '3'
> AND DATE_RETURN < '2005-04-25 00:00:00'
> This works fine in query analyser, but when I pass the same script to
> the server from an ASP page (where I am replacing the date_return value
> with an ASP variable representing todays date as above) I get the
> following message...
> "The conversion of a char data type to a datetime data type resulted in
> an out-of-range datetime value."
> Does anyone have any ideas? Maybe I'm going the wrong way about this?
> Thanks
> STR8
>|||To add to Tibor's response, you can also use a parameterized query so that
you don't need to bother with formatting SQL string literals in your code.
Hope this helps.
Dan Guzman
SQL Server MVP
<straighteight@.gmail.com> wrote in message
news:1114513868.795922.276930@.g14g2000cwa.googlegroups.com...
> Hi,
> Not sure if I have posted this is the right place, if anyone could
> point me in the direction if I'm wrong I'd be grateful.
> Anyway, heres my query, I have a SQL query which is searching records
> where a date has past, and it works fine in SQL query analyser. Heres
> an example of the query...
> SELECT
> PROD_NO,
> B.BRAND,
> PRODUCT_NAME,
> D.DATE_SENT,
> D.DATE_RETURN
> FROM dbo.PRODUCTS AS P
> INNER JOIN BRANDS AS B
> ON P.BRAND_ID = B.BRAND_ID
> INNER DATES AS D
> ON D.UID = P.UID
> WHERE STATUS_ID = '3'
> AND DATE_RETURN < '2005-04-25 00:00:00'
> This works fine in query analyser, but when I pass the same script to
> the server from an ASP page (where I am replacing the date_return value
> with an ASP variable representing todays date as above) I get the
> following message...
> "The conversion of a char data type to a datetime data type resulted in
> an out-of-range datetime value."
> Does anyone have any ideas? Maybe I'm going the wrong way about this?
> Thanks
> STR8
>|||Good point, Dan. I absolutely agree.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eb2f76lSFHA.3664@.TK2MSFTNGP15.phx.gbl...
> To add to Tibor's response, you can also use a parameterized query so that
you don't need to
> bother with formatting SQL string literals in your code.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <straighteight@.gmail.com> wrote in message
> news:1114513868.795922.276930@.g14g2000cwa.googlegroups.com...
>|||Thanks guys, the link gave me a couple of hints which lead to me
sorting it out (ie. drop the dashes and it was fine!) As for the
"parameterized query", just a newbie to SQL here (month or so...) but
will certainly look into it, thanks for the advice! :)
Cheers

Help with SQL Query - "The multi-part identifier "alias field Name" could not

Hi Everyone
This is the query and I am getting follwoing error message

"The multi-part identifier "InvDate.Account Reference" could not be bound."

SELECT MAX([DATE NOTE ADDED]) AS LASTDATE,
CC.[COMPANY],
CC.[ACCOUNT REFERENCE],
INVDATE.[LASTORDERDATE]
FROM CUSTOMERCONTACTNOTES AS CCN,
(SELECT *
FROM CUSTOMER) AS CC,
(SELECT MAX([INVOICE DATE]) AS LASTORDERDATE,
[ACCOUNT REFERENCE]
FROM INVOICEDATA
GROUP BY [ACCOUNT REFERENCE]) AS INVDATE
WHERE CCN.[COMPANY] = CC.[COMPANY]
AND CC.[ACCOUNT REFERENCE] COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS IN (SELECT DISTINCT ([ACCOUNT REFERENCE])
FROM INVOICEDATA)
AND CC.[ACCOUNT REFERENCE] COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS = INVDATE.[ACCOUNT REFERENCE]
GROUP BY CC.[COMPANY],CC.[ACCOUNT REFERENCE]
ORDER BY CC.COMPANY ASC

By the way its SQL Server 2005 Environment.
Mitesh
Well how about getting rid of:
- (select * from customer) -- just use a simple join to customer
- get rid of the collate statements in your where clauses.

Also, you'll need to add INVDATE.[LASTORDERDATE] to your group by statement.

SELECT MAX([DATE NOTE ADDED]) AS LASTDATE,
CC.[COMPANY],
CC.[ACCOUNT REFERENCE],
INVDATE.[LASTORDERDATE]

FROM CUSTOMERCONTACTNOTES AS CCN,

CUSTOMER AS CC,

(SELECT MAX([INVOICE DATE]) AS LASTORDERDATE,
[ACCOUNT REFERENCE]
FROM INVOICEDATA
GROUP BY [ACCOUNT REFERENCE]) AS INVDATE

WHERE CCN.[COMPANY] = CC.[COMPANY]
AND CC.[ACCOUNT REFERENCE] IN (SELECT DISTINCT ([ACCOUNT REFERENCE]) FROM INVOICEDATA)
AND CC.[ACCOUNT REFERENCE] = INVDATE.[ACCOUNT REFERENCE]

GROUP BY CC.[COMPANY],CC.[ACCOUNT REFERENCE], INVDATE.[LASTORDERDATE]
ORDER BY CC.COMPANY ASC|||Thanks Phill,

Your solution was just spot on.

Just out of curosity, how do you read any SQL Query, for e.g. like mine and find what is wrong.

Mitesh|||Experience, I guess. When you work with it enough, you can just "read" SQL and understand what's going on.

I really don't think you need the "select distinct [account reference] from invoicedata" query in your where clause though. You already have a distinct list from the INVDATE query in your FROM statement. Your where clause should probably be:

WHERE CCN.[COMPANY] = CC.[COMPANY]
AND CC.[ACCOUNT REFERENCE] = INVDATE.[ACCOUNT REFERENCE]sql

help with sql query

I am trying to fill a dataset with customers in Table1, but i am searching by ItemID(probably from Table3). I can't figure out the sql query for this request.
Maybe something like:"select * from Table1 ...and now I think I should some how inner join Table1 and Table3 so i can select according to ItemID.

I have the three following tables:

Table1--It's primary key is the CustID identity field:

CustID CustName CustEmail ...

Table2--It's primary key is the ItemID identity field:

ItemID ItemName

Table3--ItemID and CustID function together for the primary key of the table:

ItemID CustID
1 1
2 1
2 2
3 2
1 3
3 3

ThanksTry this

select customers.* from table1, table2,table3 where
table1.custid=table3.custid and table3.itemid=table2.itemid

HTH|||Thanks!

Help with SQL Query

Hello all. I have a query which is very simple but I just cant think of a way to do it. I have 3 tables
enrollment course
-------- ----
enrollment_id course_id
course_id course_title
ecommerce_time
enrollment_status_id
A value of 1 for enrollment_status_id = 1 means that the status is complete. Now in this query I need to get a list of all courses, a count of all the enrollments for that course_id and a count of all completed courses(enrollment_status_id=1). And then the condition is a start date and an end date(inputs). The query must be filtered on the condition
ecommerce_time >= start_date and ecommerce_time >= end_date. How do I get the 2 different counts which have different conditions? Thank you.
I have this query but I want to exclude the results which have 0 enrollments from the result set.
Select course_id, course_title As course_title,
(Select count(enrollment_id) From enrollment Where enrollment.course_id = course.course_id
And (ecommerce_time >= '01/01/2000' And ecommerce_time <= '03/01/2005')) As total_enrollments,
(Select count(enrollment_results_id) From enrollment Where enrollment_results_id = '1'
And enrollment.course_id = course.course_id And (ecommerce_time >= '01/01/2000' And ecommerce_time <= '03/01/2005')) As total_completions
From course Where organization_id = '1'
Order By course_titlePersonally, this is how I would form that query:
SELECT
course_id,
course_title,
TE.total_enrollments,
E.total_completions
FROM
course
INNER JOIN
(SELECT course_id, COUNT(*)AS total_enrollments FROM enrollment
WHERE ecommerce_time >= '01/01/2000' AND ecommerce_time <= '03/01/2005
GROUP BY course_ID) AS TE ON course.course_id = TE.course_id
LEFT OUTER JOIN
(SELECT course_id, COUNT(*)AStotal_completionsFROM enrollment
WHERE enrollment_results_id = '1' AND
ecommerce_time >='01/01/2000' AND ecommerce_time <= '03/01/2005'
GROUP BYenrollment.course_id) AS E ON course.course_id =E.course_id
WHERE
organization_id = '1' AND
TE.total_enrollments > 0
ORDER BY
course_title

The main thing here is using a derived table and to INNER JOIN onit. I also moved your other query as a LEFT OUTER JOIN although Ithink the query would have worked without moving it. To me thisapproach is easier to read and understand. I am not 100% sureabout performance.

Help with SQL Query

Hi Guys,

I need "little help" (in my books) with my SQL Query.

I have a table which lists the total transactions entered by each staff member.
All the transactions are saved with the staffID of the staff member and the date of the transaction.

Date Amount Staff
1/1/2005 12 STaff1
1/1/2005 2 STaff2
1/1/2005 1 STaff3
1/1/2005 5 STaff1

Now the problem is that I need to select All the transactions from the table grouped on the basis of data and staff member,

so essentially its should look like

Date Total Staff1 Staff2 Staff3
1/1/2005 24 12 11 1

Moreover the number of staff members can be anything, so basically I can't even use sub queries.

I have been working on this one for a while now, and still don't know what to do... any help will be greatly appreciated.

ThanxYou will need a dynamic pivot (cross-tab) function - something like:

http://www.sqlteam.com/item.asp?ItemID=2955|||hi ehorn,

Thanks for your help... worked like a charm.. :)

Cheers!