Select distinct without wanting actual field

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

PsyberFox - 18 Mar 2008 07:34 GMT

Hi there,

I have the following query:
    (select    sum(A.Quantity) QtyA
    from    Prod_Assembly_Data_1 A
    where    A.[Date] = K.[Date]
    group by A.[Date]
    ) as QtyA,
which is part of a slightly longer query running off another table (K). This
second table (A) has a date field and a route card field. Now, an operator
can process only one route card per day, but the same route card number goes
through different processes every day. I want to extract the records in such
a way that I only get one route card number per day but without putting the
route card number in the select part of the query. Is this possible? Is there
another way?

Thank you in advance...

Reply to this Message

Plamen Ratchev - 18 Mar 2008 15:40 GMT

You can use a derived table to select only distinct occurrences for each
route card for the day:

SELECT SUM(A.Quantity) QtyA
FROM (SELECT DISTINCT RouteCard, [Date], Quantity
FROM Prod_Assembly_Data_1) AS A
WHERE A.[Date] = K.[Date]
GROUP BY A.[Date]

Or if you need to summarize quantity for the day:

SELECT SUM(A.Quantity) QtyA
FROM (SELECT RouteCard, [Date], SUM(Quantity) AS Quantity
FROM Prod_Assembly_Data_1
GROUP BY RouteCard, [Date]) AS A
WHERE A.[Date] = K.[Date]
GROUP BY A.[Date]

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Reply to this Message

PsyberFox - 18 Mar 2008 15:47 GMT

Thank you kindly!

> You can use a derived table to select only distinct occurrences for each
> route card for the day:
[quoted text clipped - 18 lines]
> Plamen Ratchev
> http://www.SQLStudio.com

Reply to this Message

PsyberFox - 19 Mar 2008 08:01 GMT

Hi there,

I've actually tried your suggestions, but I still don't get what I'm looking
for. Here's my entire query:

select    top 100 percent

    [Date], month([Date]) as Month, year([Date]) as Year, sum(K.Quantity) QtyK,
    (select    sum(A.Quantity) QtyA
    from    (select RouteCardNo, [Date], sum(Quantity) as Quantity
        from Prod_Assembly_Data_1
        group by RouteCardNo, [Date]
        ) as A
    where    A.[Date] = K.[Date]
    group by A.[Date]
    ) as QtyA,
    '0' as QtyD,
    '0' as QtyC,
    (select    sum(W.Quantity) QtyW
    from    Prod_Packing_Data_1 W
    where W.[Date] = K.[Date]
    group by W.[Date]
    ) as QtyW

from    Prod_Knitting_Data_1 K

group by K.[Date]

order by K.[Date]

I am still not getting the sum(quantity) by date for the Assembly table...

W

> You can use a derived table to select only distinct occurrences for each
> route card for the day:
[quoted text clipped - 18 lines]
> Plamen Ratchev
> http://www.SQLStudio.com

Reply to this Message

PsyberFox - 19 Mar 2008 11:08 GMT

I have one more thing to mention though... which I just saw when running the
below queries separately. There is a processcode per route card per day, so
in other words, the same route card number can go through e.g. 3 process in a
single day... that's where my quantities are being summed. I actually just
need the quantity from 1 process per route card per day... can this still be
done?

> You can use a derived table to select only distinct occurrences for each
> route card for the day:
[quoted text clipped - 18 lines]
> Plamen Ratchev
> http://www.SQLStudio.com

Reply to this Message

Plamen Ratchev - 19 Mar 2008 14:45 GMT

This is why I gave you the first query with DISTINCT. Here is how it may
look integrated in your query:

SELECT [Date],
MONTH([Date]) AS 'Month',
YEAR([Date]) AS 'Year',
SUM(K.Quantity) AS QtyK,
(SELECT SUM(A.Quantity) AS QtyA
FROM (SELECT DISTINCT
RouteCardNo,
[Date],
Quantity
FROM Prod_Assembly_Data_1) AS A
WHERE A.[Date] = K.[Date]
GROUP BY A.[Date]) AS QtyA,
'0' AS QtyD,
'0' AS QtyC,
(SELECT SUM(W.Quantity) AS QtyW
FROM Prod_Packing_Data_1 AS W
WHERE W.[Date] = K.[Date]
GROUP BY W.[Date]) AS QtyW
FROM Prod_Knitting_Data_1 AS K
GROUP BY K.[Date]
ORDER BY K.[Date]

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Reply to this Message

PsyberFox - 20 Mar 2008 08:16 GMT

Hi,

I've now changed my query to be exactly like yours, and it's still not doing
what it's supposed to be doing on the assembly table... it's adding up
quantities per route card per day pertaining to all processcodes... In other
words, how do I get it to only add up one process code per route card per day?

W

> This is why I gave you the first query with DISTINCT. Here is how it may
> look integrated in your query:
[quoted text clipped - 25 lines]
> Plamen Ratchev
> http://www.SQLStudio.com

Reply to this Message

Plamen Ratchev - 20 Mar 2008 15:27 GMT

And how would you define which process code to add? Is the quantity the same
for each process code? Seems not to me otherwise the DISTINCT query should
do the job. Try this (note that is selects the MAX quantity from all process
codes for the date):

SELECT [Date],
MONTH([Date]) AS 'Month',
YEAR([Date]) AS 'Year',
SUM(K.Quantity) AS QtyK,
(SELECT SUM(A.Quantity) AS QtyA
FROM (SELECT RouteCardNo,
[Date],
MAX(Quantity) AS Quantity
FROM Prod_Assembly_Data_1
GROUP BY RouteCardNo, [Date]) AS A
WHERE A.[Date] = K.[Date]
GROUP BY A.[Date]) AS QtyA,
'0' AS QtyD,
'0' AS QtyC,
(SELECT SUM(W.Quantity) AS QtyW
FROM Prod_Packing_Data_1 AS W
WHERE W.[Date] = K.[Date]
GROUP BY W.[Date]) AS QtyW
FROM Prod_Knitting_Data_1 AS K
GROUP BY K.[Date]
ORDER BY K.[Date]

If that does not work them please post tables structure and sample data.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Reply to this Message

PsyberFox - 26 Mar 2008 09:04 GMT

Hi,
The quantities per process code might be different yes... I've changed it to
the max function, and it seems to extract what I need, so thank you so much
for your help!

> And how would you define which process code to add? Is the quantity the same
> for each process code? Seems not to me otherwise the DISTINCT query should
[quoted text clipped - 29 lines]
> Plamen Ratchev
> http://www.SQLStudio.com

Reply to this Message