You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Conor66 <co...@yahoo.ie> on 2011/07/26 21:09:29 UTC
New developer, need a little help with a subquery
Hi everyone. Ive recently started using apache derby. Im a recent graduate
> and I have a question for you more experienced developers out there. I
> would
> be grateful with any help on this. I want to make the two following
> queries
> into one, but the count(*) is giving me alot of trouble. They both work
> fine
> as individual queries. I want to see the - Number of work orders closed
> per
> day, by category. Any help on this would be amazing, I'm really stuck
SELECT "W"."id" as "Work Order Id", "AC"."id", "A"."strName" AS "Category",
day("dtmDateCompleted") as "Day"
FROM "tblAssetCategory" "AC" inner join "tblAsset" "A" ON ("AC"."id" =
"A"."intCategoryID")
INNER JOIN "tblWorkOrder" "W" ON ("W"."intAssetID" = "A"."id")
>
SELECT day("dtmDateCompleted") AS "Day", count(*) AS "Count"
FROM "tblWorkOrder"
WHERE "dtmDateCompleted" IS NOT NULL
--
View this message in context: http://old.nabble.com/New-developer%2C-need-a-little-help-with-a-subquery-tp32142730p32142730.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: New developer, need a little help with a subquery
Posted by Conor66 <co...@yahoo.ie>.
That worked right off the bat :) Thank you so much, I really appreciate it
Thomas Hill-3 wrote:
>
> if I understand correctly you need a 'group by' in your query;
> so like
> SELECT day("dtmDateCompleted") as "Day",
> "A"."strName" AS "Category",
> COUNT(*)
> FROM "tblAssetCategory" "AC"
> INNER JOIN "tblAsset" "A" ON "AC"."id" = "A"."intCategoryID"
> INNER JOIN "tblWorkOrder" "W" ON "W"."intAssetID" = "A"."id"
> WHERE "dtmDateCompleted" IS NOT NULL
> GROUP BY day("dtmDateCompleted"), "A"."strName"
>
>
>
>
>
--
View this message in context: http://old.nabble.com/New-developer%2C-need-a-little-help-with-a-subquery-tp32142730p32143148.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: New developer, need a little help with a subquery
Posted by Thomas Hill <Th...@t-online.de>.
if I understand correctly you need a 'group by' in your query;
so like
SELECT day("dtmDateCompleted") as "Day",
"A"."strName" AS "Category",
COUNT(*)
FROM "tblAssetCategory" "AC"
INNER JOIN "tblAsset" "A" ON "AC"."id" = "A"."intCategoryID"
INNER JOIN "tblWorkOrder" "W" ON "W"."intAssetID" = "A"."id"
WHERE "dtmDateCompleted" IS NOT NULL
GROUP BY day("dtmDateCompleted"), "A"."strName"