Quantcast

Reducing the output of a query

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Reducing the output of a query

Joseph L. Casale
I have a query produced from several left joins which follows the format:

XXX ItemA NULL NULL
XXX ItemA ItemB NULL
XXX ItemA NULL ItemC

I need to group the data by all columns, column 0 is trivial, however columns
1:3 can collapse when any non null field matches. In the above case this could
collapse into a single line.

How can such a query be constructed?

Thanks,
jlc

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Reducing the output of a query

Marc L. Allen
Assuming each column is either NULL or a distinct item for each group, just use MAX() of each column adding the necessary group bys.


-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Joseph L. Casale
Sent: Tuesday, May 2, 2017 3:55 PM
To: SQLite mailing list <[hidden email]>
Subject: [sqlite] Reducing the output of a query

I have a query produced from several left joins which follows the format:

XXX ItemA NULL NULL
XXX ItemA ItemB NULL
XXX ItemA NULL ItemC

I need to group the data by all columns, column 0 is trivial, however columns
1:3 can collapse when any non null field matches. In the above case this could collapse into a single line.

How can such a query be constructed?

Thanks,
jlc

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



Confidentiality notice: This e-mail is intended solely for use of the individual or entity to which it is addressed and may contain information that is proprietary, privileged, company confidential and/or exempt from disclosure under applicable law. If the reader is not the intended recipient or agent responsible for delivering the message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify the sender by reply e-mail or collect telephone call and delete or destroy all copies of this e-mail message, any physical copies made of this e-mail message and/or any file attachment(s).
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Reducing the output of a query

R Smith
In reply to this post by Joseph L. Casale
Use a substitute for the NULL values. You will have to pick a suitable
substitute that doesn't confuse the other data. In this example I just
use 0 as the substitute value, you might prefer another based on the
true nature of your query (strings are also allowed).

SELECT ItemA, ISNULL(ItemB,0) AS ItemB, ISNULL(ItemC,0) AS ItemC
...
GROUP BY ItemA, ISNULL(ItemB,0) AS ItemB, ISNULL(ItemC,0) AS ItemC


ISNULL will return the first non-null value of the two parameters
given.  COALESCE() performs a similar function and allows more parameters.


On 2017/05/02 9:54 PM, Joseph L. Casale wrote:

> I have a query produced from several left joins which follows the format:
>
> XXX ItemA NULL NULL
> XXX ItemA ItemB NULL
> XXX ItemA NULL ItemC
>
> I need to group the data by all columns, column 0 is trivial, however columns
> 1:3 can collapse when any non null field matches. In the above case this could
> collapse into a single line.
>
> How can such a query be constructed?
>
> Thanks,
> jlc
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Reducing the output of a query

R Smith
Copy-Paste dyslexia - The AS - Aliasing shouldn't be present in the
GROUP-BY clause, obviously - thanks.

[...] GROUP BY ItemA, ISNULL(ItemB,0), ISNULL(ItemC,0)


On 2017/05/02 10:06 PM, R Smith wrote:

> Use a substitute for the NULL values. You will have to pick a suitable
> substitute that doesn't confuse the other data. In this example I just
> use 0 as the substitute value, you might prefer another based on the
> true nature of your query (strings are also allowed).
>
> SELECT ItemA, ISNULL(ItemB,0) AS ItemB, ISNULL(ItemC,0) AS ItemC
> ...
> GROUP BY ItemA, ISNULL(ItemB,0) AS ItemB, ISNULL(ItemC,0) AS ItemC
>
>
> ISNULL will return the first non-null value of the two parameters
> given.  COALESCE() performs a similar function and allows more
> parameters.
>
>
> On 2017/05/02 9:54 PM, Joseph L. Casale wrote:
>> I have a query produced from several left joins which follows the
>> format:
>>
>> XXX ItemA NULL NULL
>> XXX ItemA ItemB NULL
>> XXX ItemA NULL ItemC
>>
>> I need to group the data by all columns, column 0 is trivial, however
>> columns
>> 1:3 can collapse when any non null field matches. In the above case
>> this could
>> collapse into a single line.
>>
>> How can such a query be constructed?
>>
>> Thanks,
>> jlc
>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...